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.
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.
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
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
POPSTable Spool should alert you to a potential problem I was looking at a query plan today and saw a Table Spool icon. I had never seen one of those before. I thought that it might not be so great. I found this little bit of text on spooling data to work tables and thought it was interesting.
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