You see the word GO in a lot of T-SQL scripts but do you actually know what it is or how to use it?
GO is actually not a part of T-SQL and is never passed to SQL Server. It is used by clients such as osql, sqlcmd, SSMS and Query Analyzer to control how commands are batched and sent to the server.
When you add a new LUN to a SQL Server cluster, you start by adding it as a partition under Windows Disk Management. However, SQL Server will not recognize the partition at this point. You must also add the disk resource to the dependency list for the SQL Server resource in the cluster.
DATETIME requires 8 bytes of storage and stores dates from 1/1/1753 to 12/31/9999. It is accurate to about 3 milliseconds.
SMALLDATETIME requires only 4 bytes of storage. It can stores dates from 1/1/1900 to 6/6/2079 and is accurate to 1 minute.
If you’re not in the mood to wade through BOL, you can find a quick overview with a fair amount of detail on SQL Server datatypes here.
The other day I had to uninstall SQL Server 2005 Standard Edition from a machine and reinstall it.
We had previously done an in-place upgrade from 2000 and I was never 100% convinced the instance was sound. The server is our SSIS and SSRS server. It was originally supposed to be a DEV box. But it has become more of a PROD ETL and reporting box. The only user-facing application being hosted is SSRS.
Prior to the upgrade:
Going into it, my plan for the actual upgrade:
A few things…
Instead of detaching the databases by clicking around in SSMS, it’s much faster to script out the detaches. Here is the syntax:
Using the SSMS interface you can reattach all the databases quickly so I didn’t script that out.
Although I had time to prepare, I knew a few things would shake out during the install as they always do.
I ran the install and the updates under my domain account. When I reattached all the databases, they were all owned by me. So I ran scripts to assign them all back to sa:
To enable AWE, you have to give the user you’re going to run SQL under the ‘Lock pages in memory’ privilege. To do this, go to Start > Run and then enter gpedit.msc. Navigate to Group Policy -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment. Double-click ‘Lock pages in memory’ and add your user. Click OK. I feel like I did another install recently where I didn’t have to do this step but I’m not sure. After you do this, connect to the db in SSMS and run the following:
I wanted to change the user account that SSRS ran under. Updating the user account in the SSRS Configuration Manager leads to encryption problems. I was careful to backup the encryption key before I started so I was able to follow the process detailed here http://support.microsoft.com/kb/842421 and was up and running again with little trouble. For quick reference, these 2 commands allow you to stop/start IIS through the command line:
iisreset /stop iisreset /start
The server originally had SQL Server 2005 SP2 installed along with a bunch of other SQL compoments. When I reinstalled SQL Server and tried to apply SP1, SP1 bombed when trying to update the support files. I wish I recorded the exact error but I didn’t. I’ll probably repeat this process on another box soon and if I encounter the problem I’ll provide more detail here. The workaround:
SP2 installed without a hitch.
After installing the Service Packs I also decided to run the Security Baseline Analyzer on the machine to identify new fixes. It found one I needed so I installed it. Typically I’d test it in DEV first but since this was supposed to be a DEV box in the first place and I was doing well on time I installed it. It went in without a problem.
The EXISTS and NOT EXISTS operators can often use an index whereas the LIKE and NOT LIKE operators cause a table scan. This makes EXISTS and NOT EXISTS tend to be more efficient.
Transaction log files should be optimized for sequential IO and secured against a single point of failure. Accordingly, RAID1 (mirroring) or RAID 10 (striping across mirrors) is recommended for transaction logs.
It’s true that you don’t want to continually shrink and grow the log file for a few different reasons including fragmentation and incurring the overhead of growing files during a request. However, if you have decided that your transaction log is in fact too large and needs to be shrunk, here is one way to go about it.
First, you need to determine how large your transaction log should be. The easiest way is to look at the size of your transaction log backups over a span of time. Use this script as a base and tweak as necessary.
What is the largest normal log backup? That will tell you roughly how large your transaction log should be as long as your not running into exceptional circumstances on a regular basis.
If you are running into exceptional circumstances on a regular basis, you might need to change the way you are managing the db or you might have to accept the fact that these circumstances are not as exceptional as you once thought and consider them to be within the limits of expected operation.
Once you have the size:
This should give you correctly sized logs.
You run the log backup before you shrink to make sure that you don’t have active virtual log files (VLF) that will prevent the shrink from actually reducing the file size.
File shrinks and transaction log backups are not allowed to run concurrently so you have to disable anything that would conflict with the shrink.
You change the size of the log file to make sure you have the least amount of VLFs. More VLFs can reduce performance although from what I’ve read this is more of an issue in 2000 than 2005. I have not personally seen any issues due to misconfigured log files yet though so I can’t say for sure.
I was struggling today to get a Foreach Loop Container to trawl a directory full of Access databases. The problem was that I had not correctly built the ConnectionString for my source connection using the Expression Editor. I found my answer at http://www.mssqltips.com/tip.asp?tip=1437. I’ll give you a preview: “Data Source=” + @[User::mAccessLocation] + “;Provider=Microsoft.Jet.OLEDB.4.0;User ID=;Password=;”
Keep the size of the transaction log file in mind when you are restoring a database.
Over the weekend, the backup drive on one of our servers ran out of space. Without transaction log backups, the log grew several times until it consumed the log disk. At that point SQL choked.
We straightened out the SQL problems and things ran smoothly the rest of the weekend. Today we went to restore the backup to a test server. At this point we ran into a problem I haven’t seen before but should’ve anticipated–the backup wouldn’t restore.
A little background… We run full backups every morning and transaction logs throughout the day to a backup device which writes to a single .BAK file. The .BAK file is ~80GB. The data file is ~70GB. After all the transaction log growth, the tran log was ~50GB. The staging server had enough room for the backup. I was restoring over another database so space on the data drive wasn’t an issue. However, the log drive is only about ~50GB total and there are already a few log files on there. As you can imagine, when I went to restore the database, SQL wouldn’t let me, because even though my backup was only ~80GB, the restore process was trying to create a ~50GB log file on a disk that only had ~30GB free.
I couldn’t find any way to reconfigure the size of the log file as part of the restore. What I eventually ended up doing was shrinking the log file back down to a reasonable size in prod and then doing another full backup. I am not a fan of shrinking but this is certainly one of those times where shrinking is appropriate. Keep in mind that there are certain operations that shouldn’t be run concurrently with shrinking files. You can find out more here: http://msdn.microsoft.com/en-us/library/aa213772(SQL.80).aspx (This link is for SQL Server 2000).
I was trying to install SQL Server 2005 Developer Edition on an XP SP3 workstation today but setup kept failing. The setup screen looked like this:
Looking in the log I found:
=== Verbose logging started: 6/23/2008 11:09:27 Build type: SHIP UNICODE 3.01.4001.5512 Calling process: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe ===
MSI (c) (B8:40) [11:09:27:540]: Resetting cached policy values
MSI (c) (B8:40) [11:09:27:540]: Machine policy value ‘Debug’ is 0
MSI (c) (B8:40) [11:09:27:540]: ******* RunEngine:
******* Product: {AEB9948B-4FF2-47C9-990E-47014492A0FE}
******* Action:
******* CommandLine: **********
MSI (c) (B8:40) [11:09:27:540]: Client-side and UI is none or basic: Running entire install on the server.
MSI (c) (B8:40) [11:09:27:540]: Grabbed execution mutex.
MSI (c) (B8:40) [11:09:27:540]: Cloaking enabled.
MSI (c) (B8:40) [11:09:27:540]: Attempting to enable all disabled priveleges before calling Install on Server
MSI (c) (B8:40) [11:09:27:540]: Incrementing counter to disable shutdown. Counter after increment: 0
MSI (s) (14:38) [11:09:27:540]: Grabbed execution mutex.
MSI (s) (14:14) [11:09:27:540]: Resetting cached policy values
MSI (s) (14:14) [11:09:27:540]: Machine policy value ‘Debug’ is 0
MSI (s) (14:14) [11:09:27:540]: ******* RunEngine:
******* Product: {AEB9948B-4FF2-47C9-990E-47014492A0FE}
******* Action:
******* CommandLine: **********
MSI (s) (14:14) [11:09:27:540]: Machine policy value ‘DisableUserInstalls’ is 0
MSI (s) (14:14) [11:09:27:555]: MainEngineThread is returning 1605
MSI (c) (B8:40) [11:09:27:555]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (B8:40) [11:09:27:555]: MainEngineThread is returning 1605
=== Verbose logging stopped: 6/23/2008 11:09:27 ===
After some searching on the web I discovered a solution here: http://forums.microsoft.com/msdn/ShowPost.aspx?postid=2113041&isthread=false&siteid=1&authhash=aad032b508898d0d2d15a68c05396c2b476b4c7c&ticks=633247841167440228.
Looks like the MSXML 6 installer leaves something behind that SQL Server Setup doesn’t like. The solution is to download the Windows Installer CleanUp Utility and get rid of the MSXML 6 installer files. After that—smooth sailing.