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

learn more… | top users | synonyms (2)

7
votes
2answers
4k views

sp_executesql with user defined table type not behaving correctly

Problem Is there a known issue with user defined table types as parameters to sp_executesql? Answer - no, I'm an idiot. Set up script This script creates one each of table, procedure and user ...
1
vote
1answer
431 views

Giving selective permissions in a database

I have a user [AD\dbusersgroup_limitedrights] that should have on two schema's [ICD] and [XCD] within a database only SELECT and EXECUTE rights, however for each of the users in the ...
11
votes
4answers
8k views

Query becomes drastically slower when wrapped in a TVF

I have a fairly complex query which runs in just a few seconds on its own, but when wrapped in a table-valued function, it's far slower; I've not actually let it finish, but it's run for up to ten ...
16
votes
3answers
521 views

How can I guarantee that inserts to SQL Server 2008 R2 are cached in RAM first?

Imagine a stream of data that is "bursty", i.e. it could have 10,000 events arrive very quickly, followed by nothing for a minute. Your expert advice: How can I write the C# insert code for SQL ...
6
votes
1answer
3k views

Why does it take so long to calculate an Execution Plan?

One of our customers has just upgraded to a new server. For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second. ...
2
votes
2answers
100 views

Is it Possible to have Multiple Named Instances with No Default Instance?

Using SQL Server 2008 R2, is it possible to install multiple named instances, and no default instance?
1
vote
2answers
8k views

Single User mode can't be turned off and no users are connected

I have a database that is in Single User Mode. When I run this: exec sp_dboption 'MyDb', 'single user', 'FALSE' It fails, telling me that there is still a user connected. But when I run both of ...
0
votes
1answer
185 views

Merge Replication Invalid Object ( on an object that no longer exists ) ComErrorException

My merge replication scheme has suddenly started failing. When trying to sync it complains that there is an invalid object. 'dbo.myfunction'. The function used to exist but was removed. There was ...
5
votes
3answers
6k views

SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc

I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup ...
0
votes
1answer
2k views

sp_dts_getfolder isn't in my MSDB database, any ideas why?

We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is ...
0
votes
1answer
50 views

How can I disallow restore from backup on SQL Server?

I want to disallow restore from backup. How can I do this? One Of my friends said to me "Take a certificate for your database and anyone cant restore from your backup" I searched about ...
1
vote
2answers
515 views

Can I physically split a SQL Server database?

I seem to remember some time ago reading an article saying I could split a SQL Server database into more than one file, allow me to elaborate. I have a database that has a dozen or so tables that ...
10
votes
2answers
1k views

How many Sproc Parameters is too many?

I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
3
votes
3answers
7k views

How do i set up an Alert that tells me when a replication of data failed?

I've got Database Mail setup and tested fine. Alerts do work when I stop and start the replication agent job, but I cannot get it to throw any alerts when data moves from a publisher db to a ...
1
vote
1answer
228 views

Sending mail using DatabaseMail in SQL Server 2008

I have a profile called GPMail, and previously there was an account called comments@guitarplayer.com, which was working as expected. Now I want one more account with the same site, so instead of ...
0
votes
2answers
51 views

Failure to log in using just the server name

I have more than one PC connected by LAN that I access from Server Management Studio. When I try to connect with the DB server, if I place the server IP instead of the server name in the serverName ...
2
votes
2answers
704 views

T-SQL tool that requires a semicolon to end statements

I have what is probably a rather unusual question. Is there a T-SQL tool that requires using a semicolon to terminate statements? I know that it's not required to use semicolons with SQL Server 2008 ...
1
vote
3answers
566 views

How to implement superkey sub key method on cascading tables

If I design a database with a securitymaster table that has all the common columns and then have a final column called securitytype which acts as a reference to the sub table, how is it possible to ...
3
votes
2answers
7k views

SQL Server 2008 T-SQL select hanging, but not dead locked

This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
0
votes
1answer
551 views

Coalesce Function Returning Duplicates

I have a many-to-many relationship setup between three tables. CompanyNotices (M-M) CompanyNoticesLocations (M-M) Locations I would like to retrieve all rows from the CompanyNotices table for ...
2
votes
1answer
391 views

Immediate e-mail alerting for error 229 (permission denied)

For a few reasons, I'd like to have all permission denied errors (number 229) emailed to me. Checking master.sys.messages shows that is_event_logged for this error is 0. Thus that rules out using SQL ...
5
votes
1answer
943 views

NHibernate Parameter Sniffing, SQL Server 2005 vs SQL Server 2008

NHibernate automatically uses sp_executesql with different paramters to execute it's data. Now what we're noticing in production is that we're beeing hit by parameter sniffing sometimes. In such a way ...
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 ...
13
votes
5answers
6k views

Do empty columns take up space in a table?

I have table that holds from very basic info. Just a title and a few date fields. There's one field called comments which is varchar(4000) Most of the time we leave it blank, but some times will enter ...
4
votes
2answers
2k views

Trace unclosed connections

I get timeout errors every couple of days or so between IIS7 and an SQL 2008 Server. The error message I get is: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to ...
2
votes
1answer
239 views

What's a good indexing scheme for a type-2 SCD table with a very broad natural key?

I have a table which links several fairly long text fields (~100 chars) to a single text field: { A, B, C, D } => E. The dependent value can change, so I query data daily and record A, B, C, D, ...
1
vote
2answers
76 views

SQL Server 2008 error when changing cores

I have a virtual machine (virtualbox) with Windows Server 2003 and SQL Server 2008, running with 4 cores. I wanted to use 6, but when i changed the machine configuration, for some reason SQL Server ...
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 ...
0
votes
1answer
326 views

Shrinking log file [duplicate]

Possible Duplicate: Shrinking log file Some of the sql log files (idf) are hugh like 5gb. Found a article ...
0
votes
1answer
412 views

Returning Multiple Result-Sets or Single XML Blods

I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with ...
6
votes
4answers
2k views

What is the fastest way to reset SQL Server database if you can not use RESTORE?

I have integration tests that require test database to function. Since tests should generally be independent, I reset the database in the beginning of each test. I can not use RESTORE, since some ...
2
votes
1answer
526 views

How to retrieve / read SQL Image Data which is stored in the database?

From one application, we are storing all images in SQL database in the Image datatype. From another application, i need to retrieve the images and display it on User Interface. I am able to store the ...
1
vote
1answer
778 views

Implementing Pub/Sub initiated from the database

I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is ...
3
votes
1answer
520 views

Online Re-Indexing In SQL Server 2005/2008 Standard

The Story: Once a week, we run a special Maintenance Plan that involves re-indexing 2 critical tables that have ~6 Million records between the two of them. We use SQL Server 2005 Standard Edition. ...
1
vote
2answers
465 views

Is it good practice to assign a system account for database owner?

I can see the owners of my databases usign this query SELECT name , SUSER_SNAME(owner_sid) FROM sys.databases What are the things I should consider when choosing an account for database ...
2
votes
1answer
114 views

tsql to know when a database was last shrunk

In SQL 2000, 2005 and 2008, how can I know when databases were last shrunk on a MS SQL server? I want to do this using TSQL. Regards Manjot
3
votes
1answer
3k views

To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question

We're freeing lots of space in a SQL Server 2008 R2 database -a Loooot! enough to care about- (we're dropping lots of unnecessary data). but the database file conserves its file size. we want to ...
2
votes
3answers
986 views

SQL Server tempdb question

From what I understand the system database is used to hold temporary tables, intermediate results and other temporary information. On one of my database instances I have a tempdb that is seems very ...
1
vote
1answer
845 views

IS_SRVROLEMEMBER returning NULL for valid non windows user

I have a service that executes a stored procedure to query the db for results. The stored procedure needs to use impersonation on the user who is logged in the client (since the service has its own ...
1
vote
1answer
352 views

DB Integrity Check Lock Database

Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of ...
0
votes
1answer
998 views

SQL Server 2008 - Restore Database With RESTART

I am trying to restore a database from a backup file on a SQL Server 2008 instance using the following SQL command: BEGIN RESTORE DATABASE [MyDataBase] FROM DISK=@db_location WITH ...
2
votes
3answers
3k views

Undo Database Restore in SQL Server 2008

I accidentally restored a SQL Server 2008 database with a wrong backup. Is there any way to undo this and restore to previous version?
2
votes
1answer
788 views

Exclude .LOG file while restoring a database

I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but ...
0
votes
1answer
3k views

detaching database results in error “SQL Server detected a logical consistency-based I/O error”

I'm trying to detach a database and I'm getting this error : SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:234; actual 0:0). It occurred during a read ...
1
vote
1answer
15k views

How to backup and restore a database to a different location and name via command line only

I have a database that I want to copy from one location to another (maybe even to a different computer) via the command line only. The name of the database on the new location should not be identical ...
2
votes
1answer
280 views

Table Partitioning. What is the correct process for deleting .ndf and .ldf files?

Please go easy it’s a steep learn curve.. OK steps done so far Create files, Create file groups, Create partition function, Create partition scheme, Partition table associate files with filegroups ...
0
votes
1answer
116 views

SQL Server Point in Time Restore Scenario

Hopefully you can help out on this as I may be up a creek! I am working with a database using the FULL recovery model. The database has had nightly database backups but their transaction log has ...
1
vote
1answer
2k views

Recreate MSDB for SQL Server Express 2008

My SQL Server Express database's msdb file has become 'suspect'. All the other mdf files seem to be OK. I now need to recreate the msdb file, but I do not have a backup for it (guilty as charged!). ...
1
vote
1answer
981 views

create table script with constraints in sql server 2008

I have create table scripts and alter table scripts with some constraints for that database. It take time as twice to create the table and alter the table .. Is this possible to combine create ...
8
votes
1answer
1k views

How to get accurate query performance?

I'm attempting to improve performance of a stored procedure. When I run the SP, it finishes almost instantly, as if something were cached. I was told to use the following two lines of SQL before ...