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
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.
Most Pops
see Most Pops
Clips
Pops
Comments
Tags
Print
Save
Turn on live update