Category: SQL

How to truncate Mirrored Database Log File

First backup your transaction logging:

  1. Tasks > Backup
  2. Select transactionlog backup en select a disk where to backup
  3. Now check the status with DBCC LOGINFO(DATABASENAME)
  4. Check if the last status record is 0 (zero)
  5. When the status is 2 make another backup
  6. When the status is 0 you can run: DBCC SHRINKFILE(2)

You can use this script also:
Backup LOG DBNAME
TO DISK = 'C:\SQL_TMP\transactionlog.trn'
WITH STATS
GO
DBCC LOGINFO(DBNAME)
DBCC SHRINKFILE(2)

Windows IOPS and MB/s benchmarking

You have bought een new system and the first thing what you want to do is check the disk performance (ehhh that’s always the first thing I want to know 😛 )

There  is one tool I always use: ATTO Disk Benchmark

disk-benchmark

This is a very nice tool to quickly see how fast your troughput is of your new system.

But sometimes you want to check your IOPS. Then you can use another nice microsoft commandline benchmark tool DiskSpd

You can use this parameter to benchmark:

diskspd -b8K -d30 -o4 -t8 -h -r -w25 -L -Z1G -c20G testfile.dat

This example command line will run a 30 second random I/O test using a 20GB test file located on the T: drive, with a 25% write and 75% read ratio, with an 8K block size. It will use eight worker threads, each with four outstanding I/Os and a write entropy value seed of 1GB. It will save the results of the test to a text file called DiskSpeedResults.txt. This is a pretty good set of parameters for a SQL Server OLTP workload.

Example:

diskspd
Orginal article

If you get errors like:

Results for timespan 1:
*******************************************************************************
The test was interrupted before the measurements began. No results are displayed.
Error generating I/O requests

Or file creation errors like “Error opening file: testfile.dat” please try to replace the minus “-” characters with your keyboard. Sometimes your browser copy the wrong character.

Happy benchmarking 😀

Error 29528 When Installing SQL Service Pack 4

Today I had this problem for the second time. Last time it cost me 3 hours to find the solution. Now I find your article and I fixed it in 30 minutes 🙂

My Exact error was (summary.txt):
MSP Error: 29528  The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

The workaround resolution involves the following steps:

For a stand-alone installation of SQL Server 2005

  • Remove the following registry subkeys that store SID settings:
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\SQLGroup
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\AGTGroup
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\FTSGroup
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\Setup\ASGroup

Note In these registry subkeys, MSSQL.X is a placeholder for the corresponding value on a specific system. You can determine MSSQL.X on a specific system by examining the value of the MSSQLSERVER registry entry under the following registry subkey:

  • Reinstall the SQL Server 2005 service pack or the SQL Server 2005 hotfix package.

For some reason, the local SIDs had been changed or removed or something and SQL just needed to figure out what was going on.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\

Official source: http://www.conetrix.com/Blog/post/Error-29528-When-Installing-SQL-Server-Hotfix.aspx

 

SQL Reporting Services slow with Internet Explorer

When you access the reporting services over SSL you can have the problem that the reporting services GUI under IE is very slow.

The solution is quite simple: Login with the servername or domain name. E.g: SERVERNAME\username

This problem exist under IE 8 and IE9 under Server 2008, 2008r2, Vista and Windows 7.

mysql restore (under Windows)

Hi,

If you want to use mysqldump to back up the database, you can enter the full path when typing the command, or add the path c:\wamp\bin\mysql\mysql5.0.51b\bin to the System variable PATH in Control Panel – System – Advanced – Environment Variables – System variables.

You can make a backup from the command line or a scheduled task in Windows like this, assuming that myusername and mypassword have access to the database mydatabase:

mysqldump -umyusername -pmypassword mydatabase > mydatabase.sql

You can restore the database using the mysql command from the command line:

mysql -umyusername -pmypassword -D mydatabase < mydatabase.sql

More info from the command line:
mysql –help | more

How to shrink a SQL Transaction Log

For 2005:
–* you can get the logical log file name usingthe following command in Query Analizer:

exec "databaseName".dbo.sp_helpfile

Now execute the following command to shrink the database log to 200MB:

DBCC SHRINKFILE ("logicalLogFileName", 200)
BACKUP LOG "databaseName" WITH TRUNCATE_ONLY
DBCC SHRINKFILE ("logicalLogFileName", 200)

–if it doesn’t work, run the two commands again.

–When done with that, do a full backup of your db as you will have broken your tlog backup chain.

For 2008+

USE databasename;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE databasename
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE databasename
SET RECOVERY FULL;
GO