This tag is for the 2005 version of Microsoft's SQL Server.
2
votes
2answers
261 views
How can I find databases with a large number of virtual log files?
I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis.
Almost all these databases are ...
1
vote
0answers
176 views
SQL Server 2005 Memory Pressure
We keep having a nagging problem with our SQL Server. I don't know what changed, but we started having out of memory errors every 3 weeks or so.
I thought I could locate something strange with DBCC ...
0
votes
1answer
77 views
Converting SQL Server 2005 schema to PostegreSQL 9
I have a fairly simple SQL Server 2005 schema that I exported to a tSQL file.
I am looking for a tool, preferably open source and runs on Win 7 (Ubuntu 2nd choice) that will convert the transact SQL ...
3
votes
1answer
159 views
How do I automate converting heaps into clustered indexes?
I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index.
What is the best automated method for converting these heaps?
Since ...
1
vote
2answers
95 views
For a boolean settings table, should I toggle a flag, or insert/delete?
I have a settings table.
SETTINGS
--------
settingId int
settingName varchar(20)
Each user can choose to apply the settings, or not. The interface allows them to quickly toggle the settings on ...
4
votes
0answers
270 views
Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100
I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005).
The subscription databases are also SQL Server 2008 R2, ...
3
votes
1answer
311 views
Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]
Possible Duplicate:
Determining how a schema change occurred?
I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to ...
4
votes
1answer
85 views
SQL Server 2005, Foreign key check against part of a table
I have an articles table that references a categories table. I'm defining the foreign key like this:
constraint fk_categoryid foreign key (categoryid) references categories (categoryid)
on update no ...
0
votes
0answers
99 views
How do I get the date that each user last logged onto the server / database in SQL Server 2005
Currently, I have the following code that gets the DBNAME, USERNAME, ROLE_NAME for each user on the database. I need to get the last logged in date for each user to fill in the
LASTLOGIN_DATE.
ALTER ...
0
votes
3answers
371 views
How to attach .mdf file to SQL Server 2005 without an .ldf file?
I want to attach a database to SQL Server 2005 but I have only its .mdf file
How can I attach it?
2
votes
2answers
641 views
Transaction log exploding during index reorganization [duplicate]
Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?
My client is running SQL Server 2005 x64 Standard on a beefy server with 16 GB of RAM, and separate drives ...
2
votes
1answer
41 views
SQL Server services could not be started as 2 instances sharing same 'model' file
Please advise me on this issue:
I have one default instance in SQL Server 2005 (sp 3 x64 bits) called instanceA. Then I installed another 2 instances called instanceB and C.
After that I restore ...
7
votes
2answers
222 views
Why does this cursor produce results in the incorrect order?
I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes.
The line ORDER BY 1,2,3 DESC; in the SQL ...
2
votes
2answers
195 views
Minimum/Maximum Server Memory in SQL Server 2005 with /3GB and /Userva switches
I have a 32-Bit Windows 2003 machine with 3.75GB RAM running one SQL Server 2005 instance. I am trying to optimize memory. If I use the switches in boot.ini like below,
/3GB /Userva=2490
should I ...
5
votes
2answers
202 views
What known bugs still exist in SQL Server 2005 after SP4 CU4?
I'm assuming CU4 will be the last update for SQL 2005. We intend to stay on 2005 for awhile. Does anyone know (or perhaps a list is maintained) which bugs STILL exist on 2005? I assume these would ...
7
votes
0answers
192 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 ...
0
votes
1answer
172 views
sys.dm_exec_requests does not show short lived requests?
We have a third party BI tool. The BI Tool consists of a server service that builds cubes, and an IIS Service that renders dashboards to users. The cube service and the dashboard service are ...
3
votes
1answer
340 views
DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS
I had to run dbcc checkdb repair_allow_data_loss with all_errormsgs and it's been runing over 27 hours. DB size is over 15gb and the server (windows 2008) has 32gb memory. I did run this on SQL 2005 ...
5
votes
1answer
280 views
Shrinking MDF File
I have a large database on sql server 2005.
After shrinking the data file, the database is still very big: around 9 GB.
Here is some data from sp_spaceused:
exec [dbname].dbo.sp_spaceused
reserved ...
5
votes
2answers
228 views
Performance impact of waiting on a service broker message
I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the ...
1
vote
1answer
168 views
Using sys.dm_os_performance_counters and DIFF to display logins per second
PROBLEM
Running my script below i can see the number of logins as an accumulated number to my instance:
SELECT cntr_value AS [LoginsPerSec]
FROM sys.dm_os_performance_counters
WHERE
...
3
votes
1answer
233 views
Why does using a Real data type in a mathematical equation return an incorrect result?
I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is ...
5
votes
2answers
95 views
Will a SP which writes records fail if accessed by a db_datareader role?
I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that.
Recently there ...
4
votes
1answer
428 views
Failed Upgrade from SQL Server 2005 sp2 to SQL Server 2005 sp4
SQL Server Standard 2005 sp2 32bit running on Windows 2003 Standard 32bit service pack 2.
I attempted a service pack upgrade from sp2 to sp4 using SQLServer2005SP4-KB2463332-x86-ENU.exe and received ...
6
votes
1answer
354 views
Non-yielding IOCP Listener in SQL Server 2005
Does anyone know what `Non-yeilding IOCP Listener' indicates?
On of our SQL Servers just had a bugcheck dump:
===================================================================== ...
4
votes
1answer
511 views
Mirroring - Server network address cannot be reached
I have SQL Server 2008 R2 Installed. It contains three instances.
Default (MSSQLServer)
FirstInstance
SecondInstance
All these are Log On as Network Service..
Default Instance is Principa ...
1
vote
1answer
206 views
How do I query based on percentages of a subset of values?
I have a table with a column named Type that can have some set of values. For instance, A, B, C, D, etc. Behind the scenes this is mapped to an Enum.
I need to query this table to get a percentage ...
1
vote
4answers
2k views
mdf,ldf file attach problem?
I've created one database on my local system. consider database name is test. This database primary files are test.mdf, test_log.ldf. and contains ndf files too.
Then i've stopped sql server service. ...
1
vote
2answers
102 views
How do I run a SQL Server 2005 job on Windows 7?
I have Windows 7 and SQL Server 2005 Version 9.00.1399.
I can successfully create an SSIS Package, however I am unable to execute it successfully as a scheduled job. When I run the job, SQL Agent ...
1
vote
3answers
179 views
Does Log-Shipping need Transaction Log Backup from Primary Server?
In this video tutorial it is explained that we should use the transaction log backup of the primary server database and restore it into the secondary server database for log shipping.
I was doing a ...
3
votes
2answers
124 views
Error Occured during the login process
I have Sql server 2005 on my local machine. During the studies of SQL server network configuration, I login my sql instance by my M/C IP (server name) and login 'sa' and run the following script.
USE ...
1
vote
1answer
441 views
how to get last date of today in datetime datatype?
select dateadd(s, -1, dateadd(month, datediff(month, 0, getdate())+1,0))
when i execute this query, it gives '2012-01-30 23:59:59.000'. I donno how to get it as '2012-01-30 23:59:59.997'. If anybody ...
8
votes
1answer
967 views
SQL Server TempDB behaviour in large memory environment
Reading over In-memory SQL Server log file reminded me of a question I had a short while ago.
We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of ...
4
votes
3answers
362 views
Creating new TempDB files
I would like advice on what would be the best setup for our SQL Server 2005 TempDB.
At the moment our Temp DB consists of a single MDF file of 3 GB and a single log file of 120 MB on the D: drive of ...
3
votes
1answer
354 views
Why do we have to use SWITCH in table partitioning?
I'm a beginner in SQL.
I'd like to know what is the importance of SWITCH in table partitioning?
I've created one table partitioned month-wise, with a total of 12 partitions.
The ranges are:
...
1
vote
1answer
534 views
What are the most compelling reasons to upgrade from SQL Server 2005 to SQL Server 2012?
We currently have 2 SQL Server 2005 Enterprise machines in a mirrored environment running on Dell R815 servers. These machines consist of:
Four AMD Opteron 6176 Processors (12 cores at 2.3Ghz), for ...
2
votes
3answers
181 views
selecting multiple instances of a record based on record lifespan over years
I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
2
votes
1answer
117 views
What's faster than Mirroring tables, compare or write?
A co-worker is trying to sync table B with the contents of table A, both tables have the same structure, we wrote 3 statements, one for update, one for delete and one for inserts.
I have a question ...
5
votes
2answers
241 views
Performance tuning for gap analysis of table
I have a table which stores the sequence (counter) of data received from devices out in the field. At any rate, these sequences need to be in order within a configurable time span, but can come into ...
7
votes
2answers
162 views
High rise in SQL Server CPU after adding a DB
I have just added a "new" DB to my Server (2005), I have restored from a dead SQL2000 server to my active one (2005), and changed the compatibility to 90 (SQL Server 2005).
My average CPU was on 48% ...
3
votes
1answer
531 views
How to disable case sensitivity in SQL Server 2005 for object name (Table name)?
I have created a new database in SQL Server 2005 and execute generated script from the old database.
In that I have a table called MstCity
When I try to execute the following query which has the ...
3
votes
3answers
730 views
Is there a way to test whether DELETE will fail due to constraints?
I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.
What are my options for doing this? Is there a simple way to do a "dry ...
1
vote
1answer
64 views
When creating asymetric key from assembly does assembly need to stay on the file system
I am deploying a SQL Server CLR project to my database and am usure of something. To give the function in the project external access I am creating an asymetric key from the projects assembly, ...
1
vote
2answers
110 views
Does an analysis/optimization tool like SqlYog exist for SQL Server 2005 (and later)?
I'm curious about effective space allocation of my tables, so:
does something like SqlYog exist for SQL Server 2005 (and later)?
Of course I know SSMS, but I was looking for something more precise ...
1
vote
1answer
93 views
EXISTS and sub-query cost
i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns?
1)
SELECT id
FROM Parent p
...
3
votes
3answers
610 views
What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record?
I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some ...
3
votes
1answer
94 views
Selecting 'edge' records in a sequence of data
I have a table of items which run sequentially by date with both a start and end for each entry:
ID | Start | End
----------------------------------------
1 | 2012-08-20 00:00 | ...
1
vote
1answer
122 views
Problems on filegroup size — SQL Server 2005
consider FG_january_2011 file group contains 100000 records. and FG_january_2012 file group contains 2000 records.
update Table_Name set date=dateadd(yyyy,+1,date) where Year(date)=2011
if i execute ...
1
vote
2answers
128 views
How do I create a rule for a Primary Key
I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together:
p001
b201
I know I need to create a rule and bind that rule to the primary ...
9
votes
4answers
345 views
Is shrinking a transaction log OK to do on a live database?
In Microsoft SQL Server 2005, is it OK to shrink the log file of an online database, or will it cause an interruption of service?
