1
POPSSQL Server 2005 List all IDENTITY columns When I upgraded one of my databases to MSSQL2K5 compatibility mode, I noticed that many of my insert statements on tables with identity columns started generating primary key violations. I realized that I needed to run DBCC CHECKIDENT on all of those tables to fix the problem. I found this script, which lists all of the tables, in a database, that have identity columns. Then a copy/paste and regex search/replace generated a script for me to run the DBCC CHECKIDENT for each table listed. After running the DBCC CHECKIDENT, the problem went away.
0
POPSEnabling Linked Servers While the code didn't really help me, it alerted me to the fact that you can now set these values for each linked server that you create. So I modified the properties of the Linked Server I was trying to use and all went well.
1
POPSDBCC DROPCLEANBUFFERS I saw one of my colleagues using this procedure. I haven't used it myself, but I think I'm about to start experimenting with it. I think the part about executing a CHECKPOINT first makes sense.
1
POPSIndexes have their disadvantages I've seen a lot of people talk about the consumption of disk space with respect to indexes, but I don't remember seeing mention of how it could consume memory space. I believe that makes sense. And seeing how I'm fighting with memory right now, it makes me think twice about slapping yet another index on my tables.
1
POPSfn_virtualfilestats Not exactly sure how this might help, but it's available... I found it in SQL Server Magazine's website and thought I'd clip it.
0
POPSBCP Utility I haven't used this tool in years. However, dealing with this DSS system that I'm working on has necessitated the use of the bcp utility as I'm moving about 20 GB worth of data to create a new table... It would have taken hours and probably would have filled the transaction log had I not used bcp.
0
POPSMoving the tempdb database While trying to optimize the performance of some data loads on one of our QA database servers, I decided to move the tempdb to a different disk array hoping that would improve the time it takes to assemble the large set of records to be inserted into a denormalized table. This clip contains the information I used to move the database.
0
POPSDBCC CACHESTATS One of my colleagues was using this. I had never come across it before. Thought I'd read up on it a little bit.
0
POPSData Size Converter SQL Server reports file sizes, space used and memory usage in varying increments. This handy little tool is going to help me translate between the different increments without having to think too hard about it.
0
POPSComparison Operators I don't even know why I was trying to do =<... I guess it's because I'm used to using BETWEEN more than <= >=. At any rate, this may come in handy in the future.
1
POPSYou can use an external editor with sqlcmd I haven't really done much with SQL Server 2005. I'm getting myself in the situation where I must, though, as I am using it as the platform for capturing performance counter data. I let the database grow way out of pocket and am now suffering the consequences. I know back with SQL Server 2000 you could use the osql utility to run commands and scripts from the command line. Looks like you do the same thing using sqlcmd for SQL Server 2005. I'll probably be using this to execute a command file to shrink the transaciton log when it gets too big. I'll be using Performance Monitor's Alert feature to detect when the script needs to be run and have the action be to log a record in the application event log and to execute the script to truncate the log. The other alternative is to use SQL Server Agent to execute a cleanup script in a more meaningful manner. Consider my first suggestion a failsafe.
0
POPSSort Warnings I'm pretty sure I've seen plenty of these before also. Mostly I've only used up to 4 columns in a sort, so I have a feeling that RAM is better option.
0
POPSDesign queries using SHOWPLAN turned on Yeah, this makes a lot of sense to do. I hadn't thought of it like that before. I've found myself turning Execution Plan on when working on complex procedures, but I don't make it part of my regular habit. I think this practice will be good for me to start doing.
1
POPSAvoid Temp Tables I've been working on removing the use of temp tables in some of the procedures that I've inherited. I think it's helping with the database performance.
0
POPSSet-based way of determining databases' writeability I'm writing a script that will help me "port" privileges for Windows security accounts of one domain over to equivalent accounts on a different domain. You must first check to see whether any affected databases are read-only. If they are, you must take them out of read-only mode, first, before you revoke or grant any privileges to the database. I am using this procedure to figure out which databases are read-only.
0
POPSOracle RAC Overview Oracle RAC technology sounds pretty cool. I've worked with Microsoft SQL Server clusters before, but never with Oracle. I'm interested in learning more, especially since it's looking like I'll need to deal with Oracle a lot more in my next job. :-)
0
POPSUsing stored procedures to submit events to NS So based on what the tutorial is saying, I'd assume that it would be possible to have event data sent to the NS application from another database using DTS or a replication publication. It seems likely that you could also use a Message Queuing application to insert event data.
Newest Clips
see Newest Clips
Pops
Comments
Tags
1
Most Pops
Print
Save
Turn on live update