Hello
Am trying to create a script that will determine the amount of space is been used on both my file server and sql server for all jobs residing on an instance. Once this result has been determined, I will like to populate them into another table, which will be used to develop a report model, using SSRS.
Am using the xp_fixeddrives query, but it only returns the drive letter and the amount of free space.
Can anyone provide me with any ideas, pls?
I import about six 30,000-line text files to my database daily using a few DTS's - is there a way to "turn off" the transaction log for these imports to conserve disk space? I do not need to log these inserts...
Thanks.
I'm trying to run the Disk Usage default report and am getting an error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
An error occured while rendering the report.
------------------------------
ADDITIONAL INFORMATION:
An error occurred during local report processing. (Microsoft.ReportViewer.WinForms)
------------------------------
The definition of the report 'Main Report' is invalid. (Microsoft.ReportViewer.Common)
------------------------------
An unexpected error occurred in Report Processing. (Microsoft.ReportViewer.Common)
------------------------------
Cannot execute a program. The command being executed was "C:WINDOWSMicrosoft.NETFrameworkv2.0.50727vbc.exe" /noconfig @"C:Documents and SettingsAdministratorLocal SettingsTemp8vikhk2b.cmdline". (System)
Report has been running fine up until now. No changes that I'm aware of. Report runs fine on my laptop connecting to the server.
Update: This is happening with other reports as well. (i.e. Performance Dashboard)
I am having a problem with the disk usage reports after creating a centralised MDW on SQL Server 2008, this is the report displayed when you drill down onto a specific database.
When I drill down on a database on the local server to the MDW database the disk usage report is shown correctly.
However, when I drill down onto another servers database I receive the following error: A data source instance has not been supplied for the data source 'DS_TraceEvents'.
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
Hello,
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
John
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Thanks in advance. Hail SQL Server!
Hello,
this is my configuration :
1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.
What will happen to the SQL data (DB) when the disk that holds the paging file crashes?
Kindest regards,
Luc.
Hi all,
Ok here goes,
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
Thanks
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
hi there,
i need to transfer the data off a query output to a txt file in the same order as it prints. i have tried using the command
eg.
exec bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword
this is exactly what i used replacing the appropriate parameters, but it gave me a "syntax error near queryout"
PLEASE advice what is wrong or sugest the best method to achieve the same.
Any help would be appretiated.
Thanks,
c: )
I need to provide some infomation on how much (trans/request)SQL7 can handle.
I checked the white papers and testimonials, but don't see any actual numbers.
We have a clustered SQL7 environment sitting on some Compaq 6400 using 4 cpu's. Our database size is only about 3.5 gig and we are using IIS 3. Does anyone know where to get this information?
Please advise
Thanks
Susan
Hi,
We have a SQL Server 2000 Enterprise Edition running as production
The CPU Usage is High when there were >350 user connections.
The buffer hit ratio is good. I checked all the monitor.
Can anyone can guide me why it is causing.
Thanks in advance,
Anu
Hi guys, I have a sell server its a new server 4 processors and 4 gigs of ram. SQL server is pinning the CPU's 100% I can’t figure out why, I'm at mdac 2.7 with the hotfix. I don’t know what else to look at.
Any ideas?
Hi .... I'd like to know if I can use BCP to transfer the whole database to a device or file and then back to the server(I mean,using only one server) with a new char set/sort order configuration,or I have to do it table by table??? How do I use BCP to perform this task?What commands and parameters should I use???
Thanks for your attention,
Luciano
we have CPU Usage upto almost 100% i increased the tempdb, and master to almost double the size
then we increased the memory to high amount.
does anyone have suggestion pls help since it running too slow, since we did an upgrade
I have a SQL 2000 Server, dual processer with hyperthreading. 2Gb of Ram. The machine is only being used as a SQL Server
Normally the server runs at about 15-20% usage. I have now noticed a problem where the SQL server suddenly jumps to 100%, this can happen after a week or a couple of days. It requires a server reboot to fix the problem. Stopping the SQL server and restarting will not work.
I don't know if this is part of the problem, but I have noticed that after a day or so the processor usage climbs by about 10%, if I stop the SQL service and restart, the processor usage drop by the 10%.
Also I have set the memory usage to be 1.5Gb, but it takes a day or two for the SQL server to consume this amount of memory. Don't know if this has anything to do with it.
Anyone know why this is happening ?
Kev
Hi All.
I am new to MSSQL, Iam using it to store largeamounts of data on a daily basis,that I import from a CSV file at the rate that I am going it should be about 1Gig a month of data. I noticed that as I add data to MSSQL my ram usage climbs by the size of the data. Is there someting Ihave done wrong inthe setup.
Thanks
Scott
I've got a copy of SQL2005 and IIS on the same machine. Sql Server seems to be eating a lot of RAM, eventually causing IIS to restart.
Anybody had any luck playing with max server memory ?
hi all,
when I click web page (executing some stored procedure generally will take less than 2 minutes) the CPU usage is becoming 100% and taking a long time to run.I already posted a forum before (SQL server 2005 running slow ). I dont know these problems are related.If I restart the server then it will run as ususal.What should be problem.Server Windows 2003 ,SQL server 2005
Is it bcos of any memory lekage or any other reason..
Thanks
leo
HiI am having a real issue with CPU usage by SQL Server, and it is notrelated to a poor query.I have a clients database that I am currently investigating some issueswith. After I perform a standard task using the application, and theresults have been returned to the application the cpu usage remains at100%.Even once the application has been completely closed down the cpu usageremains at 100%. Nothing else is happening.I am at a complete loss as to how to proceed to with investigation ofthis issue (i have been looking at this for over a week using SQLServer tools and Performance Monitor, and eliminating various otherpossibilities)I downloaded Process Explorer, and looking at the threads forsqlservr.exe there is one in particular that is consuming all of thecpu time:MSVCRT.DLLI am running SQL Server 2000 SP4 on the following machine:Windows 2000 SP4Pentium 4 3Ghz (Note this is seen as 2 processors so the reported cpuusage is 50%)1Gb MemoryI also have about 20Gig of free disk space.One other thing, the page faults reported on the Performance tab ofProcess Explorer exceeds 3 million. This is after running theapplication for around 10 minutes.Please can anybody suggest anything at all that might help? I'm sorrythere is not too much information in here but I have not been able tofind out anything useful!Many Thanks in advance.Paul
View 22 Replies View RelatedHi guys,Got an odd SQL string that I need to produce that is most probably simple toconstruct but with it being hot in our office, I simply can't get my headaround it....!!Its based around an online emailing facility whereby multiple hotels can beemailed via a single application. Users within the application have accessrights to email only specific hotels.The tables are laid out like this (irrelevant columns left out)...CampaignID, CampaignName, CampaignHotelIDs1 Test Campaign 1,4,5,7,92 Test Campaign2 1,2UserID, UserName, UserHotelIDAccess1 Test User 1,6,72 Test User 2,7Now on the stats page I want to give users access to view ONLY sentcampaigns to which they have access to view, I was considering the IN SQLstatement to achieve something like this...'WHERE CampaignHotelIDs IN UserHotelIDAcess'....but that doesn't want to work, can anyone give me any ideas to get thisworking within just a single SQL query?Cheers, @sh
View 7 Replies View RelatedHi,
I have 2 SQL Server 2005 instances in one server. We noticed that CPU usage is high in the server. Is there is any posibility to know how many percentage each SQL Server instances is taking?
LIke Total usage is 60%.
Instance 1 is 40%
Instance 2 is 20%
regards
Wilson Thomas
Hi all,
I want to write a backup application for SQL server.I have read the VDI specifications. I want to know whether I can use the VDI just to freeze and thaw without taking the snaphot and the backup. Or is there any other way to do the same ?
Thanks in Advance.
We will move t-log from one disk to another one. I tried Alter Database and found it did not work for log.
Can somebody give me a method about moving log from different disks?
Thanks.
Jean
Hello
I need to setup a compaq sever with 300 MB database, and will be adding around 600 records on a daily basis. Can someone help with how much disk space i should have on sqlserver, providing i have c: and d: setup.
Thank you in advance for all the help.
Paula
Hi All,
I have a server and it has C: D: F: I: Drives and all the system files are on C:Drive and and all the .MDF's and .LDF's(model,temp,master) are on the F: Drive and now I am running out of space on both(C: and F: Drives)
1. Can we add space to the C: and F: drives on the fly?.
2. Can I move the System databases ( MDF's and LDF's to some other drive)and if so, how do I do it?( Moving the databases ) and this is on the production database so when I have to do this.Will there be any impact.
Thanks in Advance,
Reddy
Does anyone know how to get the free disk space for all drives in many different SQL servers.
I need to populate a report (right now output in Excel) with the free disk space of all drives on all of my SQL servers.
I found xp_fixeddrives but that is specific for the server where it is executed.
Any help or pointers to a script in the archives is much appreciated.
Thanks.
One of my production servers has been determined to be 92% fragmented.
What's the proper procedure for defraging a database server?
I couldn't find anything very helpful in BOL, nor Knowledge Base.
Sidney Ives
Database Administrator
Sentara Healthcare
We need a drive cloning app, like Ghost, that will allow us to transfer a production image off of a HDD to other PCs. The problem with this is that
our production needs SQL in order to run and transferring the initial image to uniquely named workstations causes DB registration problems. Is there
an app that will allow us to configure this transferred image so that SQL will refer to the "new" drive instead in of the "old"?