SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

10
votes
2answers
7k views

Data type for storing an array of flags (a bitmap/bit array)

I need to store a bit array for each record of a table, supporting the following operations: Testing if a bit is set, and setting a bit (using SQL) Querying and setting the value using ADO 2.8 (not ...
8
votes
4answers
18k views

Replicating a remote MySQL database to MS SQL Server 2008

I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database. Is this possible? Can anyone outline the steps required in order to achieve this? Thanks.
7
votes
2answers
1k views

How to schedule jobs in Sql server 2008 for less than 10 sec?

I want to run a job every 3 seconds, however in SQL Server 2008 we cannot define an interval of less than 10 seconds. The job is used to insert/update visitor information, and segmentation ...
11
votes
1answer
1k views

Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?

Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered ...
9
votes
3answers
863 views

Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
8
votes
4answers
729 views

How can I know which store procedure or trigger is using a table on SQL Server 2008 R2?

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...
4
votes
6answers
17k views

SQL Server log shrinking Issue [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? I have a 300 GB database log in our SQL Server. I want to shrink all the logs but it is not allowing me to ...
3
votes
2answers
3k views

Error: Cannot Generate SSPI context

When someone tries to connect to a SQL Server instance, the error shows up: It's not possible do generate SSPI context. Yesterday we had a blackout (don't know how to say this expression in ...
3
votes
1answer
1k views

Backing up file groups with restore to earlier backup

I have a task: I have created a database with a filegroup lamb that has a few tables. I need to extract a csv into a table in a filegroup named lamb and back up that filegroup. Then extract another ...
8
votes
2answers
3k views

How to backup/restore SQL Server database from command line similar to mysql

I need to automate the migration for some databases from an older server. I know that script a tool using C# for example can be a possibility but I need a simple, fast and effective solution in order ...
8
votes
2answers
594 views

Mirroring the Distributor

Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an ...
5
votes
1answer
869 views

How to get text for default language

I have so tables: and so data at Language table: and so data at Text table: I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
3
votes
1answer
437 views

Shrinking log file

Some of the sql log files (idf) are hugh like 5gb. Found a article (http://extended64.com/blogs/rhoffman/archive/2009/06/11/shrink-sql-server-2008-database-log-file-script.aspx) but not sure why he ...
27
votes
4answers
41k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
14
votes
5answers
5k views

Possible attack on my SQL server?

Checking my SQL Server log I see several entries like this: Date: 08-11-2011 11:40:42 Source: Logon Message: Login failed for user 'sa'. Reason: Password did not match for the login provided. ...
9
votes
2answers
8k views

why is io_stall_writes_ms so much higher for tempdb?

We have the user and system data files on the same disk drive. The ( io_stall_write_ms / ( 1.0 + num_of_writes ) ) is below 2 for the user files but the tempdb files are typically over 400. I see ...
7
votes
1answer
28k views

SQL Server permissions to create, restore, delete only some databases

On MS SQL Server 2008 R2, I need to have user which can create, restore, DBCC CHECKDB and then drop databases. But he must not be able to access or delete specified databases on server (security ...
7
votes
1answer
29k views

Finding the last time a table was updated

The query: SELECT name AS TableName, create_date AS CreatedDate, modify_date as ModifyDate FROM sys.tables order by ModifyDate; ...will tell me the last time a table was created ...
21
votes
3answers
5k views

Efficency of stored procedures vs raw queries

I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would ...
11
votes
2answers
7k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
10
votes
2answers
80k views

Listing the existing SQL Server Logins and Users

I know we can check the logins and the users that are defined, using GUI in SQL Server, but am wondering how we can do this check using script. I ran the query below but it shows Principal_id which ...
10
votes
2answers
7k views

How and when does SQL Agent update next_run_date/next_run_time values?

I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific ...
8
votes
2answers
2k views

SQL Disk Setup Advice - TempDB, Log DB, Data file placement question

We have a very active database server with a ecclectic collection of applications running on it. Two of the busiest are a Laserfiche database that does document scanning and workflow processing all ...
7
votes
1answer
13k views

Restore encrypted database to another server

I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. ...
22
votes
8answers
11k views

How do long columns impact performance and disk usage?

In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on. In reality, how much does it really matter? How ...
17
votes
2answers
22k views

How to change the instance name of SQL Server Express 2008?

On my development machine I've installed SQL Server Express 2008, but it has the instance name .\SQLEXPRESS. I want it to be the default instance. I'm pretty sure that I selected the "Default ...
13
votes
2answers
6k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
13
votes
5answers
99k views

Writing select result to a csv file

We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option. I ...
9
votes
5answers
12k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
9
votes
2answers
4k views

How can I help SQL Server recognize my indexed view column is NOT NULL-able?

I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes): CREATE VIEW dbo.balances WITH SCHEMABINDING AS SELECT user_id ...
6
votes
2answers
3k views

When was a Database taken Offline

I am trying to find a way to figure out when was couple of my sql databases were taken offline. I checked the the logs but could not find any such info and moreover there is no default trace enabled. ...
6
votes
3answers
273 views

Check server activity with code

I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the ...
6
votes
3answers
1k views

What could cause the wrong ID to be inserted?

I have a SQL Server 2008 server (build 10.0.5500). Earlier this week I ran this on a table that already had data in it: delete from dbo.table go dbcc checkident('dbo.table',reseed,0) When the ...
6
votes
3answers
10k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
4
votes
3answers
778 views

Why does my procedure run slowly until I update statistics?

I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the ...
4
votes
2answers
6k views

Problem Reaching VM SQL Server Instance

Here's what I'm working with right now. I have a host OS which includes a SQL Server 2008 Enterprise instance. I have a virtual machine running on this host which has a SQL Server 2008 R2 Express ...
2
votes
2answers
4k views

How to find memory usage by table? [duplicate]

I want to discover how much memory is being used by each table. Is this information available in DMVs or elsewhere?
2
votes
3answers
3k views

SQL Subquery , One to Multiple rows

Following query I have written returns the result as expected, example 121350 rows. SELECT dbo.Articles_in_Consignment.Consignment_id, dbo.ORDER_D.Orders_boxsize, ...
11
votes
2answers
4k views

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do. I ...
10
votes
2answers
6k views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
10
votes
2answers
4k views

Restore bak file to smaller mdf and ldf database files

I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have: MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB This ...
9
votes
2answers
969 views

Hard limitation for merge replication article count?

A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
8
votes
4answers
19k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
8
votes
2answers
2k views

Shared Lock issued on IsolationLevel.ReadUncommitted

I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared) ...
8
votes
5answers
4k views

Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
8
votes
2answers
6k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
7
votes
2answers
6k views

Transaction Log maintenance on Mirror database

SQL Server Version: 2008 R2 Enterprise SP2 I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
7
votes
2answers
1k views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
6
votes
1answer
1k views

Index seek much slower with OR condition compared with separate SELECTs

Based on these questions and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table Large table with historical data allocates too much of SQL Server 2008 ...
6
votes
4answers
7k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...