Simulate SQL Server Activity on a Disk Subsystem with SQLIOSim from Microsoft

The SQLIOSim utility simulates the I/O patterns of Microsoft SQL Server 2005, of SQL Server 2000, and of SQL Server 7.0. The I/O patterns of these versions of SQL Server resemble one another. The SQLIOStress utility has been used to test SQL Server 2005 I/O requirements for many years.

For more information and to download SQLIOSim utility from Microsoft see KB231619.

NOTE: The SQLIOSim utility replaces the SQLIOStress utility. The SQLIOStress utility was formerly named the SQL70IOStress utility.

SQL Server 2005 Remote Connectivity

1. Enable remote named pipe or tcp: All programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration -> Configuration for Services and Connections -> Remote Connections, choose either enable TCP or Named Pipe or both.
2. Open Firewall TCP Port 1433 for SQL Database Engine.
3. Open Firewall UDP Port 1434 for SQL Browser Service.

How to get only hour from getdate() function in sql server

select datepart(hour,getdate()) as [hours]

DatePart Function will retrieve part of the date.

Syntax:
DATEPART(part, datetime)

Part
----
Ms for Milliseconds
Yy for Year
Qq for Quarter of the Year
Mm for Month
Dy for the Day of the Year
Dd for Day of the Month
Wk for Week
Dw for the Day of the Week
Hh for Hour
Mi for Minute
Ss for Second

You can replace part with the any of the part in the specified above items.

For example, for hours:

SELECT DATEPART(Hh,getdate())

Format SQL statements with HTML to look like SQL Server Management Studio

Have you ever tried to post a SQL statement in a blog or web page, but cannot figure out how to format it to look like it does in SQL Server Management Studio or Query Analyzer?

Compare this statement:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.BAK'

To this one formatted to appear like SQL management tools:

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.BAK'

Here's the HTML - just substitute your commands.

Line 1 (BACKUP DATABASE AdventureWorks):

<code><span lang="EN-US" style="color: blue; font-size: 10pt;">BACKUP DATABASE</span></code> <code><span lang="EN-US" style="color: black; font-size: 10pt;">AdventureWorks</span></code><span lang="EN-US" style="color: black; font-family: 'Courier New'; font-size: 10pt;"></span>

Line 2 (TO DISK = 'C:\Backups\AdventureWorks.BAK'):

<code><span lang="EN-US" style="color: blue; font-size: 10pt;">TO DISK =</span></code> <code><span lang="EN-US" style="color: red; font-size: 10pt;">'C:\Backups\AdventureWorks.BAK'</span></code>

Using Transact SQL to get length of TEXT field [T-SQL]

To calculate the lenght of a TEXT field the LEN function used to calculate the length of VARCHAR fields won't work.

You need to use the DATALENGTH T-SQL function:

SELECT DATALENGTH(myTextField) AS lengthOfMyTextField

Log Parser 2.2 - work with IIS log files and more

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®.

Download LogParser 2.2 from Microsoft.

Get help with LogParser in the LogParser Forums.

More on LogParser

• Log Parser: The coolest tool Microsoft has ever Released!
  
• Using Microsoft Log Parser to search event viewer for critical system errors
  

MS SQL Backup Database to Disk

To create a full database backup using Transact-SQL

Execute the BACKUP DATABASE statement to create the full database backup, specifying:

1. The name of the database to back up.
2. The backup device where the full database backup is written.

Example

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\Backups\AdventureWorks.BAK'

SQL: How to extract Year, Month, Day, Hour, Minute and Seconds from a DateTime

The DATEPART function accepts two parameters :

DATEPART ( datepart , date ) where
datepart - specifies the part of the date to return. For eg: year, month and so on
date - is the datetime or smalldatetime value

QUERY

SELECT
DATEPART(year, GETDATE()) as 'Year',
DATEPART(month,GETDATE()) as 'Month',
DATEPART(day,GETDATE()) as 'Day',
DATEPART(week,GETDATE()) as 'Week',
DATEPART(hour,GETDATE()) as 'Hour',
DATEPART(minute,GETDATE()) as 'Minute',
DATEPART(second,GETDATE()) as 'Seconds',
DATEPART(millisecond,GETDATE()) as 'MilliSeconds'

Note: When using a smalldatetime, only information up to the 'minute' gets displayed. Seconds and milliseconds are always 0.

SQL Server count items per day or per hour

Here are a couple of quick SQL statements to return counts based on entries or items or transactions per day or per hour. You can tailor as desired.

-- Count items per day

SELECT Year, Month, Day, COUNT(Day) AS "Items per Day"
FROM
(
SELECT DATEPART(YEAR,CreatedOn) Year, DATEPART(MONTH,CreatedOn) Month,
DATEPART(DAY,CreatedOn) Day
FROM tableName
) temp
group by Year, Month, Day
order by Year desc, Month desc, Day desc

------------------------------------------------
-- Count items per hour

SELECT Year, Month, Day, Hour, COUNT(Hour) AS "Items per Hour"
FROM
(
SELECT DATEPART(YEAR,CreatedOn) Year, DATEPART(MONTH,CreatedOn) Month,
DATEPART(DAY,CreatedOn) Day, DATEPART(HOUR, CreatedOn) Hour
FROM TableName
) temp
group by Year, Month, Day, Hour
order by Year desc, Month desc, Day desc, Hour desc

Keywords:
sql server something per day
sql count per hour
sql count per month
sql count time of day
"sql server" count
sql server day count
count per hour in sql
count records per hour per day sql
day,hour,minute format in sqlserver
entries per day sql select

SQL SERVER – Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime

Get Current Date & Time

select GetDate()

SQL Server 2000/2005

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

Display List of MS SQL Database Tables

use <DatabaseName>
select * from information_schema.tables

Example:

use myDatabase
select * from information_schema.tables
order by table_name

Shrink (truncate) Microsoft SQL transaction log files

Shrink (truncate / purge) MS SQL transaction log files.

Use <dbname>
Go
alter database <dbname> set recovery simple
go
dbcc shrinkfile (<dbname>_log, 100)
go
checkpoint
go
dbcc shrinkfile (<dbname>_log, 100)
alter database <dbname> set recovery full
go

Example:

Use myDatabase
Go
alter database myDatabase set recovery simple
go
dbcc shrinkfile (myDatabase_log, 100)
go
checkpoint
go
dbcc shrinkfile (myDatabase_log, 100)
alter database myDatabase set recovery full
go

View logical log filename and physical location for MS SQL database files

View logical log filename and physical location (current DB only).
select physical_name from sys.database_files where type = 1

View logical log filename for all DBs:

select a.name, b.name as 'Logical filename', b.filename from sys.sysdatabases a
inner join sys.sysaltfiles b
on a.dbid = b.dbid where fileid = 2

View SQL DB log size and space used

View DB log size and space used.
use
dbcc sqlperf(logspace)

Example:
use myDatabase
dbcc sqlperf(logspace)


MSSQL

Command line reference: Database and Operating Systems.

Great resource for Windows and Linux command line utilities as well as SQL, Oracle and Windows PowerShell.

http://ss64.com/index.html

MSSQL: View actively running commands by SPID

--- View actively running commands by SPID.

select session_id, Text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) t

Troubleshooting
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation... must be serialized