Accessing a windows share from a Mac (ugh!)
smb://username@servername.fqdn/sharename
We were then prompted for the password, and presto!
Source : Apple Support - http://support.apple.com/kb/ht1568
Thursday, 29 July 2010
Wednesday, 28 July 2010
Backup all Filegroups
A short script I wrote to backup all filegroups has been featured on SQLServerCentral.com today.
SSC : Backup all Filegroups
SSC : Backup all Filegroups
Tuesday, 27 July 2010
Bookmark : Security Tools
SQLBF - Brute force or dictionary password, from a hash.
http://www.cqure.net/wp/sqlpat/
SQLAT - SQL Auditing Tool
http://www.cqure.net/wp/sql-auditing-tools/
MSSQLScan - Find SQL Server instances
http://www.cqure.net/wp/mssqlscan/
* Links are supplied for information only. These are for testing your OWN servers, networks & passwords! *
http://www.cqure.net/wp/sqlpat/
SQLAT - SQL Auditing Tool
http://www.cqure.net/wp/sql-auditing-tools/
MSSQLScan - Find SQL Server instances
http://www.cqure.net/wp/mssqlscan/
* Links are supplied for information only. These are for testing your OWN servers, networks & passwords! *
Monday, 26 July 2010
SQL 101 : Tempdb
This post explains exactly what tempdb is responsible for -
Iain Kick - Not another tempdb post
And what to do to tune it, namely -
1) RAID level (& separate version)
2) Instant File Initialization
3) Trace Flag T1118
4) Pre Size tempdb
5) Split tempdb per processor core
Iain Kick - Not another tempdb post
And what to do to tune it, namely -
1) RAID level (& separate version)
2) Instant File Initialization
3) Trace Flag T1118
4) Pre Size tempdb
5) Split tempdb per processor core
Friday, 23 July 2010
Filegroup Backups
A quick runthrough experimenting with Filegroup Backups
So , to use this technique, you can't use SIMPLE recovery mode.
Why am i using SIMPLE? - It's a development box!
Setting to FULL resolves this -
successfully processed 21281 pages in 8.921 seconds (18.636 MB/sec).
Using the system table sys.filegroups, i can build a list of backup commands to backup all filegroups, like this ...
BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' TO DISK = 'c:\Adventureworks.BAK'
Msg 3004, Level 16, State 1, Line 1
The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
So , to use this technique, you can't use SIMPLE recovery mode.
Why am i using SIMPLE? - It's a development box!
Setting to FULL resolves this -
ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT GO BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' TO DISK = 'c:\Adventureworks.BAK'
Processed 21280 pages for database 'Adventureworks', file 'AdventureWorks_Data' on file 2.
Processed 1 pages for database 'Adventureworks', file 'AdventureWorks_Log' on file 2.
BACKUP DATABASE...FILE=Using the system table sys.filegroups, i can build a list of backup commands to backup all filegroups, like this ...
DECLARE @backupPath VARCHAR(500) SET @backupPath = 'd:\sqlbackups\' DECLARE @backuptimestamp VARCHAR(30) SET @backuptimestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_') SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @backupPath + @backuptimestamp + '_' + DB_NAME() + '_' + name +'.BAK''' + ' WITH COMPRESSION ' AS BACKUPCOMMAND FROM sys.filegroups
NB : My commands feature the compression setting as am on SQL 2008 Enterprise.
Thursday, 22 July 2010
Bookmark : SQL Server DMV Starter Pack E-Book Published
The SQL Server DMV Starter Pack is a download comprising of an E-Book & Scripts for 28 DMV based queries. It has been put together by Glenn Berry, Louis Davidson and Tim Ford and is available as another freebie from Redgate
The 28 queries it provides, are -
Connections, Sessions, Requests, Queries
1: Are you Connected?
2: Session Ownership
3: Current expensive, or blocked, requests
4: Query Stats – Find the "top X" most expensive cached queries
5: How many single-use ad-hoc Plans?
6: Ad-hoc queries and the plan cache
7: Investigate expensive cached stored procedures
8: Find Queries that are waiting, or have waited, for a Memory Grant
Transactions
9: Monitor long-running transactions
10: Identify locking and blocking issues
Databases and Indexes
11: Find Missing Indexes
12: Interrogate Index Usage
13: Table Storage Stats (Pages and Row Counts)
14: Monitor TempDB
Disk I/O
15: Investigate Disk Bottlenecks via I/O Stalls
16: Investigate Disk Bottlenecks via Pending I/O
Operating System
17: Why are we Waiting?
18: Expose Performance Counters
19: Basic CPU Configuration
20: CPU Utilization History
21: Monitor Schedule activity
22: System-wide Memory Usage
23: Detect Memory Pressure
24: Investigate Memory Usage Across all Caches
25: Investigate memory use in the Buffer Pool
Other Useful DMVs
26: Rooting out Unruly CLR Tasks
27: Full Text Search
28: Page Repair attempts in Database Mirroring
Redgate : Download SQL Server DMV Starter Pack
The 28 queries it provides, are -
Connections, Sessions, Requests, Queries
1: Are you Connected?
2: Session Ownership
3: Current expensive, or blocked, requests
4: Query Stats – Find the "top X" most expensive cached queries
5: How many single-use ad-hoc Plans?
6: Ad-hoc queries and the plan cache
7: Investigate expensive cached stored procedures
8: Find Queries that are waiting, or have waited, for a Memory Grant
Transactions
9: Monitor long-running transactions
10: Identify locking and blocking issues
Databases and Indexes
11: Find Missing Indexes
12: Interrogate Index Usage
13: Table Storage Stats (Pages and Row Counts)
14: Monitor TempDB
Disk I/O
15: Investigate Disk Bottlenecks via I/O Stalls
16: Investigate Disk Bottlenecks via Pending I/O
Operating System
17: Why are we Waiting?
18: Expose Performance Counters
19: Basic CPU Configuration
20: CPU Utilization History
21: Monitor Schedule activity
22: System-wide Memory Usage
23: Detect Memory Pressure
24: Investigate Memory Usage Across all Caches
25: Investigate memory use in the Buffer Pool
Other Useful DMVs
26: Rooting out Unruly CLR Tasks
27: Full Text Search
28: Page Repair attempts in Database Mirroring
Redgate : Download SQL Server DMV Starter Pack
Wednesday, 21 July 2010
Windows 2008 : Hyper-V - Disappearing Client for Microsoft Networks
Two years ago, i posted about a networking eccentricity with Hyper-V where the 'Client for Microsoft Networks' would disappear from the clients.....
An easier way to resolve this is as follows >
1) Go to Network Connections -
2) Press ALT to display the menu bar and select 'Advanced Settings' from the revealed 'Advanced' menu.
3) Select the virtual NIC (Local Area Conection 4 in my screenshot). Tick the Client for Microsoft Networks check box and the 2 boxes under it for IPv4 and IPv6.
MSDN Forum
On inspecting the Local Area Connection Properties, ‘Client for Microsoft Networks’ is unticked. Simply clicking the tickbox appears to work fine, but when you click OK , the following appears-
" Your current selection will also disable the following features: Client for Microsoft Networks "
" Are you sure you want to disable these feature(s)? "
1) Go to Network Connections -
2) Press ALT to display the menu bar and select 'Advanced Settings' from the revealed 'Advanced' menu.
3) Select the virtual NIC (Local Area Conection 4 in my screenshot). Tick the Client for Microsoft Networks check box and the 2 boxes under it for IPv4 and IPv6.
MSDN Forum
Tuesday, 20 July 2010
SSIS , OLEDB and Stored Procedures
SSIS , OLEDB and Stored Procedures.
SSIS cannot 'see' the metadata for returned data from stored procedures like it can for tables and views. Here is a collection of methods to try when using sprocs with SSIS.
On oledb connection managers, set -
On data sources and components inside the data flow, set -
When calling your stored procedure, prefix as follows -
In the stored procedure itself, create a header as follows -
SSISTalk: Phil Brammer - Stored Procedures and the OLE DB Source
ReplicationAnswers : Coping with no column names in the oledb source
SSIS cannot 'see' the metadata for returned data from stored procedures like it can for tables and views. Here is a collection of methods to try when using sprocs with SSIS.
On oledb connection managers, set -
DelayValidation = True
On data sources and components inside the data flow, set -
ValidateExternalMetaData = False
When calling your stored procedure, prefix as follows -
"SET FMTONLY OFF; EXEC dbo.myprocedure @param = 1"
In the stored procedure itself, create a header as follows -
CREATE PROCEDURE dbo.myprocedure (@param INT) AS BEGIN -- {options to set...} SET NOCOUNT ON; -- {supress 'rows affected' messages} SET FMTONLY OFF; -- {ensure full data retuned. incase FMTONLY ON was in effect} -- {false header to pass metadata} IF 1 = 0 BEGIN SELECT CAST(NULL AS INT) AS [intColumn1] , CAST(NULL AS INT) AS [intColumn2] , CAST(NULL AS VARCHAR(5)) AS [varcharColumn1] , CAST(NULL AS VARCHAR(255)) AS [varcharColumn2] , CAST(NULL AS DATETIME2(0)) AS [datetime2Column1] , CAST(NULL AS VARBINARY(20)) AS [varbinaryColumn1] END -- {genuine query} SELECT intColumn1, intColumn2, varcharcolumn1 ... END GO
SSISTalk: Phil Brammer - Stored Procedures and the OLE DB Source
ReplicationAnswers : Coping with no column names in the oledb source
Saturday, 17 July 2010
SQL Server Processor Configuration
AFFINITY controls processors and threads used by SQL server,
MAXDOP controls the maximum number of processors a single query can use.
solace : Affinity & Affinity I/O
solace : Max Degree of Parallelism (MAXDOP)
MAXDOP controls the maximum number of processors a single query can use.
solace : Affinity & Affinity I/O
solace : Max Degree of Parallelism (MAXDOP)
Friday, 16 July 2010
SQL 2008 on Windows 2008 R2 / Windows 7
Quick notes :
To install SQL 2008 on Windows 2008 R2 you must install the application server role first!
To successfully run SQL 2008 on Windows 7, make sure you install Service Pack 1,
Andrew Fryer's blog : SQL Server 2008 on Windows 7 / Windows server 2008 r2
Softpedia : Make Windows 7 Play Nice with SQL Server 2008
Joes Barreto : Changes in Roles, Role Services and Features from Windows 2008 to Windows 2008 R2
Wednesday, 14 July 2010
Bookmark : Troubleshooting SSPI errors : Detecting a bad SPN
'Advanced Troubleshooting Week at SQL University, Lesson 1' caught my eye today.
Not because of it's catchy title, because of the content (which i could have done with 2 years ago).
It's on SPNs (service principle names) those Active Directory entries needed for Kerberos authentication and I heartily recommend it.
Bookmarking here, so I find it again....
SSC: Troubleshooting SSPI errors - Detecting a bad SPN
Not because of it's catchy title, because of the content (which i could have done with 2 years ago).
It's on SPNs (service principle names) those Active Directory entries needed for Kerberos authentication and I heartily recommend it.
Bookmarking here, so I find it again....
SSC: Troubleshooting SSPI errors - Detecting a bad SPN
Bookmark : Error Line Numbers
I have always found stored proc error line numbers in Management Studio somewhat confusing.
They never match what you see on screen!
The link below explains it fully, but in short -
"its the line in the original batch that compiled the procedure" so, "it includes the comments and the white space before it"...
Link : What line does the error line number refer to?
They never match what you see on screen!
The link below explains it fully, but in short -
Link : What line does the error line number refer to?
Tuesday, 13 July 2010
Bookmark : How do you learn BI as a DBA?
Johnathan Kehayias has published something very much at the forefront of my thoughts right now, an article entitled How do you learn BI as a DBA?
Having earned my MCITP Database Administrator 2008 and MCITP Database Developer 2008 last year, I'm keen both to certify in BI , expand my SSIS development work and get to grips with SSAS development.
The following articles are referenced in Jonathan's post and will serve as my starting point too.
Planning Your First Microsoft BI Solution
Building a Data Foundation for a BI Solution
Building Your First Cube
Having earned my MCITP Database Administrator 2008 and MCITP Database Developer 2008 last year, I'm keen both to certify in BI , expand my SSIS development work and get to grips with SSAS development.
The following articles are referenced in Jonathan's post and will serve as my starting point too.
Planning Your First Microsoft BI Solution
Building a Data Foundation for a BI Solution
Building Your First Cube
Monday, 12 July 2010
Scripting Data to a .CSV format
A simple way to script example data, so it can be posted in forums etc...
SSC : How to post data/code on a forum
SELECT 'SELECT ' + QUOTENAME(column1,'''')+',' + QUOTENAME(column2,'''')+',' + QUOTENAME(column3,'''') + ' UNION ALL' FROM myschema.mytable
SSC : How to post data/code on a forum
Sunday, 11 July 2010
SQL Server Default Trace
SQL Server Default Trace. Useful to tell who did what and when!
-- Get info about the default trace (includes the trace file location) ...
-- Show everything you can get from querying the default trace ...
-- Query the default trace ...
Links :
There's Something about SQL! : Default Trace
MSDN : Default Trace
-- Get info about the default trace (includes the trace file location) ...
SELECT * FROM ::fn_trace_getinfo(default)
-- Show everything you can get from querying the default trace ...
SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description FROM ::fn_trace_geteventinfo(1) t INNER JOIN sys.trace_events e ON t.eventID = e.trace_event_id INNER JOIN sys.trace_columns c ON t.columnid = c.trace_column_id
-- Query the default trace ...
SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName, e.category_id, cat.name, textdata, starttime, endtime, duration, eventclass, eventsubclass, e.name as EventName FROM ::fn_trace_gettable('D:\Data\MSSQL10.MSSQLSERVER\MSSQL\Log\log_152.trc',0) INNER JOIN sys.trace_events e ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
Links :
There's Something about SQL! : Default Trace
MSDN : Default Trace
Thursday, 8 July 2010
Creating a 'System' Stored Procedure
Want to create a procedure that is available in any database (and will run in the context of that database) ?
- Create the procedure in the master database
- Prefix the name with sp_
USE master GO CREATE PROCEDURE sp_myproc AS BEGIN SELECT DB_NAME() AS CurrentDatabase END GO USE tempdb GO EXEC sp_myproc GO
Are my statistics up to date?
SELECT s.name AS SchemaName , t.name AS TableName , i.name AS IndexName , i.type_desc AS IndexType , STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate FROM sys.objects t INNER JOIN sys.indexes i ON i.[object_id] = t.[object_id] INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] WHERE i.name IS NOT NULL AND s.name <> 'sys' ORDER BY STATS_DATE(i.[object_id], i.index_id) , t.name, i.type
Wednesday, 7 July 2010
Long Running Queries
A colleague sent me this to show what is currently running.
Look at the WHERE clause to adjust it for the duration threshold you are interested in.
From Measure TSQL Statement Performance , this query provides performance statistics for cached query plans.
Look at the WHERE clause to adjust it for the duration threshold you are interested in.
SELECT r.session_id , p.kpid , r.start_time , DATEDIFF(SECOND, r.start_time, GETDATE()) as elapsed_time , st.text , r.status , r.command , r.cpu_time , r.wait_type , DB_NAME(r.database_id) , p.hostname , qp.query_plan FROM sys.dm_exec_requests AS r INNER JOIN sys.sysprocesses AS p on r.session_id = p.spid CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp WHERE session_id > 50 AND DATEDIFF(SECOND, r.start_time, GETDATE()) > 10 -- duration in seconds ORDER BY r.start_time
From Measure TSQL Statement Performance , this query provides performance statistics for cached query plans.
SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY total_elapsed_time / execution_count DESC;
Monday, 5 July 2010
SQL 101 : SQL 2008 Compression
Backup Compression
Table & Index Compression
- Comparison : Comparing backups with and without compression
- Enabling Backup Compression by Default
- Backup Compression Optimizations
Table & Index Compression
- Table compression basics
- TSQL - Commands to compress database tables
- TSQL - Commands to compress database indexes
- TSQL - Compress all tables and all indexes (Original | Updated - 2016)
- TSQL - Compress uncompressed tables and indexes
- TSQL - List compressed / uncompressed objects
- TSQL - Minimising Compression Rebuild Time
- Paul Nielson's procs to estimate and perform table compression are here.
- Real world tryout (performance implications)
Thursday, 1 July 2010
Fast Delete !
Performance wise, a very fast delete can be achieved using a VIEW utilising an ORDER BY clause!!!
Create the view -
Run a while loop to delete the data effeciently in batches -
Link : SQLCAT - Fast ordered delete
Create the view -
CREATE VIEW [dbo].[del_data] as SELECT TOP(500) * FROM dbo.data WHERE id < 219150348 ORDER BY id GO
Run a while loop to delete the data effeciently in batches -
WHILE(1=1) BEGIN DELETE dbo.del_data IF @@ROWCOUNT < 500 BREAK END
Link : SQLCAT - Fast ordered delete
Subscribe to:
Posts (Atom)