I am seeing select * from sys.sysprocess where blocked<>0 is always the report server
ReportServer.dbo.GetSessionData;1 is blocked by ReportServer.dbo.WriteLockSession;1
We have different reporting servers using same database.
Is it require to rebuild indexes on report server and report server temp db on daily basis?
How to solve the below blocking issue
One of our production server is configured with Always on with sql cluster.
We are getting frequently blocking
Waiting Resource Key Type: Key
WaitType: LCK_M_S
WaitResourceDatabaseName : ReportServerTempDB
WaitingSessionProgramName: Report Server
BlockingSessionProgramName: Report Server
WaitCommandType: Select
WaitingCommandText: Create Procedure dbo.checksessionLoak @sessionID...
We are getting frequently blocking in Report server database.
Is it ok to change isolation level to RCSI for report server database?
Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?
how to know the limitation of number of objects(Maximum no.of objects allow tempdb database) in a tempdb database?
View 2 Replies View RelatedWe are noticing a lot of blocking when performing bulk inserts.
Sometimes the lead blocker(s) are blocking inserts into the same tables, but most of the time, it's insert to other tables. So, while bulk insert into table X is running, bulk insert into table Y is blocked from the insert into X.
- We turned off transactions from the application performing the bulk inserts - no change.
- We enabled table locking from the application - no change
- We enabled lock_on_bulk_load on the tables - this seems to have worked; however still see some blocking.
According to the lock_on_bulk article - When you specify table locking for a bulk import operation, a bulk update (BU) lock is taken on the table for the duration of the bulk-import operation, shouldn't we be seeing this BU lock? Instead, we see nothing but LCK_M_X (exclusive table locks).
Just found out that in order get a BU lock, no indexing can exist on the table ... sure be nice if that was in the article!
Also, we saw the exclusive locks happening before we made these changes, meaning it wasn't using the row locks that it should be by default. Assuming we're missing something here with lock escalation. I am profiling just for lock escalation, and it never happens ...
So I guess my pending question at this time is that why when inserting into table X do inserts into table Y get blocked?
SQL Server 2008, Tempdb ("ReportServerTempDB")of the report server is growing enormously over 20gb in 3 days. All our reports drive from stored procedures which is a different server from Reportservertempdb. We have no report subscriptions.
We store no snapshots. All we do is run the reports.
I don't know where I start to investigate the issue.
Seen activity like this? If so, where does it come from?
dd hh:mm:ss.mss:00 00:18:32.210
session_id:79
login_name:meme
wait_info:(6ms)IO_COMPLETION
CPU:646,180
tempdb_allocations:2,088
tempdb_current:0
blocking_session_id:NULL
reads:171,237,095
writes1,439,934
physical_reads:637,080
used_memory:2
status:rollback
I created a Blocking Event Notification following this article ->[URL=
How do I throttle the alerts so that I only receive one alert per hour instead of 1000?
In sql 2012 i want to track the blocking details at a specific time is any query to get the data ?
View 6 Replies View RelatedI want to implement a script which will run continuously to find out blocked sessions and send an email when ever it finds the blocking.
View 5 Replies View RelatedAt one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
It is possible that Data Collection can cause massive increasing MB/sec to tempdb ? I cannot find connection with tempdb and I set cash file, but on same disk.
Or it can be something different? Last two weeks what I checked was Read/Write MB/s to tempdb increasing progressively.
One time it was about 20MB/sec
After it was reseting and again 1MB/sec..
What I checked , External company which install SQL Server made one file for tempdb, next week or during breaktime(it will be possible), I would like make 8files next weekend work.
Now I saw that TempDB mdf was still increased, but using was just 8-10%
I have added ndf to tempdb for checking performance improvement.... Now I want to remove the ndf file... I am using below command...
USE tempdb
GO
DBCC SHRINKFILE (3, TRUNCATEONLY);
GO
use master
go
ALTER DATABASE TEMPDB Remove FILE tempdev1
Results:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
23766476643232
Error:-
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
Note:
=>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error...
=> I executed above commands and restarted services...no change...
How to remove / drop ndf file...
I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.
I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down...
I can get a snapshot of tables in tempDB, but I would like to track which procs are causing the load in the tempDB.
I think I can sample and record objects in the tempdb, but I would like to record the proc creating the most tempDB usage, and disk read/writes associated with those procs.
The DMV's give usage in the individual DB's, but what's a good way to correlate procs in the DB's to tempdb usage?
I have a server which is not running optimally and I checked the default trace. I have around 600 entries in the default trace which are all Missing Column Statistics and the database is tempdb.is_auto_create_stats_on and is_auto_update_stats_on are both 1 for tempdb.
View 2 Replies View RelatedIs it possible to encrypt Master database and tempdb? On executing below query result is showing temdb is encrypted.
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
So we have new servers that are going to be installed with SQL 2012 and I'm debating the wisdom of splitting tempdb with multiple files.
I know it's a myth that performance automatically improves if you split it into a number of files based on processors, but I'm debating the wisdom of putting a file on each of my data / log file drives.
For instance, I have a server with a C: drive (OS), D: drive (Data for system DBs and install of programs - 458 GB), an F: drive for user DB data files (767 GB), and a J: drive for log files (255 GB).
Obviously no files are going on C:. I'm debating on whether or not we should even leave system DBs on the D: drive given in our current 2k8 servers, we end up with Memory.dmp files over flowing the D: drives as well as .cabs and other install / update files that tend to collect on that drive over the years.
But if we leave the system DBs on D:, I'm wondering if adding a second tempdb file to F: and a third to J: will improve query performance or not.
While trying to move the tempdb log file to a different disk, I mistakenly executed the following and then stopped and restarted SQL Server.
USE master
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'L:MSSQL11.MSSQLSERVERMSSQLData emplog.ldf')
GO
Now I can't start SQL Server and see this error in the log files:
[code]
2015-08-10 15:28:42.55 spid7s Error: 5171, Severity: 16, State: 1.
2015-08-10 15:28:42.55 spid7s L:MSSQL11.MSSQLSERVERMSSQLData emplog.ldf is not a primary database file.
2015-08-10 15:28:42.55 spid7s Error: 1802, Severity: 16, State: 4.
2015-08-10 15:28:42.55 spid7s CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.
[code]
I did not have remote connections enabled yet, so the resolutions I have found that include sqlcmd or starting in single user configuration are not working. Any way that might allow me to restore the usual tempdb settings, which I think would allow SQL to start again?
I need a script to Backup & Shrink tempdb.
namesize
tempdev1024
templog64
tempdev21024
tempdev31024
tempdev41024
tempdev51024
tempdev61024
tempdev71024
tempdev81024
I can't believe how many tempdb's there are?
I have a tempdb split into 4 files (5 if you include the log).
Autogrowth is disabled on the mdf/ndf files so that they can be used round robin (1 file per logical CPU).
Is there a way to be alerted when there is x% of free space left?
I know hwo to check the free space via t-sql but want to be able to be alerted. I could run a sql job that reports the free space and send a database mail message if under x% but wondered if there was a built in (or better) method?
I also have SQL Sentry.
SQL 2012 Standard
We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
I was in the process of creating additional TempDB.ndf files, and received an error saying they already exist. I checked the location and it was empty, nothing to see here. So I looked in sys.master_files and there are several tempdb files listed in various locations, all of which come up empty.
So the files are listed as online in sys.master_files, but they do not exist on the server. I restarted SQL services but it did not change anything.
We have SQL Server 2012 EE SP2 server that is getting hit by reports that time-to-time use up to 300GB of tempdb.
It's happening because our clients can use 'bad' parameters for some reports.
Is there a way to autokill sessions that overuse tempdb (for example, if a session uses more than 15Gb in tempdb)?
Killing can happen immediately or after regular check (5 min for example).
I receive Error: 3967, Severity: 17, State: 1. Insufficient space in tempdb to hold row versions. We have 8 data files for temp db of 10210 GB size and given 10240 GB as max size.
As MS suggest to calculate the temp db file size and growth rate we need to monitor the perform counters Free Space in Tempdb (KB) and Version Store Size (KB) in the Transactions object.
basic formula: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction
My report disk utilizations says tempdb is full ? I thonk I need a shrink for the file .
Still I am confused in calculating the size , My perform counter gives me data as such
Free Space in tempdb (KB) 279938496
Version Generation rate (KB/s) 53681040
Version Cleanup rate (KB/s) 53422320
Version Store Size (KB) 258720
Version Store unit count 22
Version Store unit creation 774
Version Store unit truncation 752
I am currently investigating aa high avg write time ms issue (145ms) which seems to be only occuring on the tempdb data files.I have followed the recommended setup of TEMPDB in that
1. Data files = number of physical cores
2. Data files and logfiles are on separate partitions away from the other databases.
3. Tempdb is presized and no incremental file increases look like they are happening with frequency.
We have sharepoint 2012 setup on other sql servers and with TEMPDB setup following the same guidelines, with far more Sharepoint activity on a similary specified hardware which is why its confusing.FileIO auditing on the partitions themselves shows that the FileIO is very fast on the partitions that the tempdb data file which leads me to beleive that Sharepoint may be the culprit perhaps due to excess use of tempdb with operations taking a long time to resolve.
While trying to fetch report ,getting below error.
Title Object reference not set to an instance of an object.
System.NullReferenceException: Object reference not set to an instance of an object.
at Pagal.ReportManager.Web.UI.FolderTree.get_SelectedFolderID()
at Pagal.ReportManager.Web.View.ReportManagement.EditReport.VerifyItemDoesNotExist(String item)
at Pagal.ReportManager.Web.UI.Validation.SSRSNameValidator.SubClassEvaluateIsValid()
[code]....
Because of the way in which a specific piece of code is written, I'm bound into using a WHERE clause for a report generation.Each Inspection generates a unique Inspection Number. Any re-inspection created from that inspection is assigned that Inspection Number and appended with ".A", ".B", ".C" and so on.
The problem is this: Each row's Primary Key is the "InspectionId" in "dbo.v_InspectionDetailsReports". I need to return not only the data related to that particular InspectionId, but also the data related to any previous related inspection. For example, if I have a main number of CCS-2012 and three re-inspections, CCS-2012.A, CCS-2012.B and CCS-2012.C, and I report on CCS-2012.B, I need all the data for CCS-2012, CCS-2012.A and CCS-2012.B but NOT CCS-2012.C.
I would prefer to not have to do everything in a WHERE statement, but my hands are a bit tied.
The "SELECT * FROM dbo.v_InspectionDetailsReports WHERE . . ." is already hardcoded (don't ask).
SELECT *
FROM dbo.v_InspectionDetailsReports
WHERE ( RefOnly = 0
OR RefOnly IS NULL
[code]...
I am trying to edit the 3 facets found under Report Server while logged onto the server as an administrator. This is one of 3 (supposedly) identical Report Servers (Windows Server 2012 Standard - Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) ) and the only one experiencing an issue. The images below show what happens when I try to change the facets to TRUE and their original values. Incidentally, when first hitting the facets, they are not set to true even though the config file has all 3 values set to true.
[URL]
I want to write a query to generate a report. I have a date column which will be holding all the business dates. No dates of Saturday and Sunday are allowed. What I am looking for is, I want to get the result of every 5th business day of each month. A month could start with any day. I just want only the 5th business day.
View 9 Replies View RelatedI have a powerbuilder application that is connecting to SQL Server. We get very frequent DeadLock situations. Is there a way that I can check to see as what SQL is causing the Lock?
Thanks in advance for any suggestions...
-Raj