Howto: Shrink MS SQL Database Transaction Log

As some hosting provider do limited the size of MS SQL database to be hosted, you might found out your database is not really used up the allocated size.

For many cases, it is because your database transaction log used up those spaces and you might not need to have this transaction log for any purpose. To get this resolve, you may shrink the database transaction log.

1. Open the MS SQL Management Studio and connect to the console.
2. Run the following command from SQL Query.

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

** Replace the DatabaseName and TransactionLogName with your database information.

In most of the cases, you may schedule this task to be run automatically if needed.

MSSQL: The server principal is not able to access the database

If you accessing MSSQL 2005 with MSSQL 2008 management studio, you might getting error of The server principal is not able to access the database when log into the database server.

To fix it,
1. Connect to SQL Server Management Studio.
2. Connect to the SQL server.
3. Click “View” and then select on the “Object Explorer Details”. (This step could be done from shortcut F7) Continue reading MSSQL: The server principal is not able to access the database

How to check the version of SQL Server Native Client

For certain purpose, you might want to check the version of SQL Server Native Client from your server. But how could you find the version of SQL Server Native Client installed from your server?

To find it out, you may use the registry editor to find it out.

For SQL Server 2008/SQL Server 2008 R2,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI10\CurrentVersion

For SQL Server 2005,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Native Client\CurrentVersion

Restrictions on Backup Operations in SQL Server

Today, I just noticed that the SQL Server backup is not generated and it is kinda weird.

After looking around from MSDN, it is found out there are some restriction from the backup operation.

– Backup not running due to any databases in offline status.
– Concurrency restriction during backup.

For more information, you may refer to the URL as below.
http://msdn.microsoft.com/en-us/library/ms175477%28v=SQL.90%29.aspx

Changing MSSQL Ownership of a Table

Sometimes, you are moving your SQL Server database from one server to another server and you found out the connection string is correct but the database could not be connected.

You may check if your schema set correct from your MS SQL server. To done it, you may refer to the step as below.

1. Open the SQL Query Windows.
2. Use the command as below.
==================
USE mydatabase;
GO
ALTER SCHEMA dbo TRANSFER [schema].mytable;
==================
# replace schema with your old schema, mytable with your table name.
3. Execute it.

You will now found the database is connected.

To Identify your SQL Server Version

Some times, it is very hard for you to check your SQL Server version even log into the console as the about of SQL Server did not actually mentioned the actual version.

To get it done, you may run a query from the SQL Management where this is applied to SQL Server 2008, 2005 and 2000.

Open your SQL Server Management and enter the following query to the New Query file.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Result:

Automate SQL Server Task

When you running SQL Server, it is impossible for you to back up your database manually daily. Hence you will need some tool to schedule your SQL server to backed up daily.

With SQL server, you may easily backup your database without any extra tools but using the SQL Agent services to back up your task.

Step 1: Open SQL Server Agent and expand Job Activity Monitor.

Step 2: Right click and select new job. Enter the necessary information and click on the check box.

Step 3: Put your command into the command box and enter the necessary information.

Step 4: Schedule the date and time or routine time to run the task.

SQL: Select

Assume that we have a table named MyTable and it contain 4 column named ID, Name, Date and City.

Today, I will show some SQL Select command that might useful to SQL Administrator daily task.

1) SELECT * FROM MyTable – Returns all columns/Rows from MyTable
2) SELECT * FROM MyTable WHERE ID=2 – Returns the row(s) which ID has value 2
3) SELECT * FROM MyTable WHERE ID BETWEEN 10 and 20 – Return all row(s) between 10 and 20, this result includes 10 and 20
4) SELECT * FROM MyTable WHERE City IN (‘KL’,’Penang’,’Johore’) – Returns all row(s) which city is KL, Penang, Johore
5) SELECT * FROM MyTable WHERE Name LIKE ‘A%’ – Return all row(s) where the name starts letter A
6) SELECT * FROM MyTable WHERE Name LIKE ‘%A’ – Return all row(s) where the name ends letter A
7) SELECT * FROM MyTable WHERE Name LIKE ‘[ABC]%’ – Return all row(s) of name start with A / B / C
8) SELECT * FROM MyTable WHERE Name LIKE ‘[^ABC]%’ – Return all row(s) of name not start with A and B and C
9) SELECT (Name+space(1)+City) AS Address FROM MyTable – Returns single column address, name and city added together with a space
10) SELECT * FROM MyTable WHERE Name IS NULL – Return all row(s) which Name has null values
11) SELECT * FROM MyTable WHERE Name IS NOT NULL – Return all row(s) which Name has not null values
12) SELECT * FROM MyTable Order By ID Desc – Sort the result set descending order, Asc or not using any sort Ascending order
13) SELECT ‘Visual Studio’ as IDE, ‘2010’ as Version – Creating memory resident result set with two columns[IDE and Version]
14) SELECT Distinct ID FROM MyTable – Returns unique rows based on ID
15) SELECT TOP 10 * FROM MyTable – Return 10 customers randomly
16) SELECT getdate() – Shows the current date
17) SELECT db_name() – shows the database name which you are working on
18) SELECT @@Servername – Shows name of the server
19) SELECT serverproperty (‘Edition’) – You can pass following ServerName, Edition, EngineEdition, ProductLevel to get current information about the server
20) SELECT user_name() – Get current user
21)  SELECT *  into #test FROM MyTable  – Create temporary table #test and insert all records FROM MyTable
22)  SELECT Max(ID) FROM MyTable – Returns Maximum ID FROM MyTable
23)  SELECT * FROM MyTable Compute Max(ID) – Returns two result sets – getting all rows and maximum value of ID

Prevent SQL injection by using IIS URL Rewrite

Nowadays, a bad or unoptimizes SQL query could easily compromised or get attacked. However you may try to reduce or prevent (Don’t say avoid, it is not possible, keke) the SQL injection through your expression rules.

If you are using IIS as your web engine, you may use some expression to reduce the SQL injection.

[dD][\%]*[eE][\%]*[cC][\%]*[lL][\%]*[aA][\%]*[rR][\%]*[eE][\s\S]*[@][a-zA-Z0-9_]+[\s\S]*[nN]*[\%]*[vV][\%]*[aA][\%]*[rR][\%]*[cC][\%]*[hH][\%]*[aA][\%]*[rR][\s\S]*[eE][\%]*[xX][\%]*[eE][\%]*[cC][\s\S]*

Continue reading Prevent SQL injection by using IIS URL Rewrite