Recently I was performing an OS upgrade on one of our DB servers, moving from Server 2003 to Server 2008. The DBMS is SQL Server 2005. While reinstalling SQL on the new Windows installation, I went to another of our DB servers to verify a couple of settings. Now, I always thought this second server was Server 2003 x64 + SQL 2005 x64 (from what I’d been told), but I now have my doubts about [...]

We have a SQL Server 2005 box running Windows Server 2003 R2 Std 64bit. We have two production databases residing on one partition (which is an iSCSI software target), that partition got apparently got corrupted and the databases are not working properly and pulling all kinds of consistency check errors. We have created a new iSCSI target and introduced it to the SQL server as a new disk partition. we have good backups that we [...]

I am running a simple query in SQL Server 2005 and want it to export to a file via sqlcmd. I was hoping to get the results in a csv format without the headers and the query metadata (how many rows are affected). for the headers you can actually specify -h -1 but how can you get rid of the ending text? Right now i have sqlcmd -S klingon -d stardb -i C:\testscript.sql -o C:\testresults.csv [...]

When viewing Queries by CPU usage with Performance Dasboard, I notice a great deal of queries like “Select * from dbo.sometable”. What is the best way to go about tracking the source of these queries? Nowhere in my code do I run a select on a full table, but they continue to appear and SQL says they are getting executed ever few minutes. Are these actual queries or is it possible that they are being [...]

I would appreciate if someone could help me with the following problem: We use two SQL Server 2008 R2 databases under transactional replication: transactional publication with updatable subscriptions. because we run out of disk space we need to move the database files into a new drive. But I don’t want to break the replication. What I’m looking for are the required steps that will help me to move the files to the new drive. Thanks [...]

I am trying to install SSIS on our production SQL 2005 SP2 box. Each time I try, the install/setup screen results in failure, starting with the native client, and moving on down. Screen shots below show what I see: Here is the result of clicking on the status link to the right of the native client after the install failed: === Verbose logging started: 3/28/2012 16:38:08 Build type: SHIP UNICODE 3.01.4000.4042 Calling process: C:\Program Files\Microsoft [...]

I’m trying to do a large INSERT operation from a database to another. But I get an error message: Msg 9002, Level 17, State 4, Line 1 The transaction log for database ‘_ARCHIVE_mydatabase’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases Things I’ve done so far: Checked that the disk has enough disk space checked that the transaction log file size is small (1024 [...]

It appears that CLR integration does not like Dotnet framework 2.0 sp2. I am getting the following error message when trying to run code that depends on the clr. Msg 6510, Level 16, State 11, Line 1 Common Language Runtime (CLR) v2.0.50727 not installed properly. The CLR is required to use SQL/CLR features. I have verfied with the dotnet verification tool that the dotnet framework for all versions installed work. I have only found possibly [...]

I have a SQL Server 2005 instance running on a production server. CPU load is relatively low in part thanks to some recent improvements in the code. I’m trying to work out if I could safely reduce the number of CPUs in the machine and would like to test that out before actually getting them removed. Is it possible to tell SQL Server 2005 to just use say 4 of the 8 CPUs in the [...]

I have written a PS1 script that 7-zips a file and uses PuTTY’s SCP to store it at my back-up server. I have tested it and it works perfectly. Then I log into my SQL Server Agent and add an extra step to my back-up job to execute that script as soon as it’s done with its back-up job. It’s called correctly, the .7z file is created, and pscp.exe is called… and halts. I go [...]