Hi
I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).
What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.
Cheers
We have 350 tables in our database, I want to drop/delete those table(s) that are inactive. Is there a way to check which tables are not active, so I could drop/delete that table(s).
View 1 Replies View RelatedHere are two tables. How do i create an SP for INACTIVE delete and how do i use the UNIQUE constraint to avoid duplication of the NAME columns(logingroup_name and login_name) in these tables?
--Creating Table ref_LoginGroup:-
create table ref_LoginGroup
(
logingroupid tinyint identity(1,1) constraint pklogingroupid primary key clustered,
logingroup_name varchar(25) constraint chkname check(logingroup_name NOT LIKE '% %'),
changed_login_id int,
is_active bit,
lastupdr smalldatetime,
)
--Creating Unique nonclustered Index for ref_LoginGroup:-
create unique nonclustered index idxlgn on ref_LoginGroup(logingroup_name)
--Creating Table ref_Login:-
create table ref_Login
(
login_id int identity(1,1) constraint pkloginid primary key clustered,
login_name varchar(50) constraint chklogname check(login_name NOT LIKE '% %'),
firstname varchar(30),
lastname varchar(30),
logingroupid tinyint constraint fklogingroupid foreign key references ref_LoginGroup(logingroupid),
changed_login_id int,
is_active bit,
lastupdr smalldatetime
)
--Creating Unique nonclustered Index for ref_Login:-
create unique nonclustered index idxl on ref_Login(login_name)
My db is working in simple recovery mode, when I run "dbcc sqlperf(logspace)", I find the log file is very large and the log usage percentage is 98%. In most case, when I run "backup log db_name with no_log", the log usage percentage will be reduced. but in fact it is no change. I make sure there are no active logs in it. Who can tell me the reason ? thx.
View 1 Replies View RelatedI have a snapshot & a transcation publication on Server A, and a pull subscription for each on Server B.
The distribution agent on ServerA has the error:
"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."
Any idea what caused this ..... Everything was fine for a week or so.
Does anyone know of any good books or publications on Replication ? I've got some DBA books that cover the basics, but I'd like something that helps explain the oddball situations.
SQL 2K
Hello,
I am having a problem with the SQL Server 2005 database of our ERP system. We have a scheduled batch that gets stuck because of a lock on a table. The lock remains overnight and halts all other processes (it seems most of them are waiting for this lock to get solved)
I know which table the lock is on and if I kill the lock manually, the batch continues and finishes without a problem
my question is: can I change this behaviour somewhere?
The piece of code that causes this lock is protected, but in a debug log I was able to see that it happens during following actions:
first a select is performed to fetch a certain record in the file that gets locked
next an update is performed with following syntax:
update ... set.... where current of 'pointername'
this update is waiting for a lock to be released before the batch continues.
My guess is that somehow the pointer that is set with the select statements, locks the row and that's why the update cannot be performed.
The strange part is that this lock does NOT occur everytime that this table needs to be updated. And that's why I am stuck and asking help of the real database people :)
If someone can give me a hint on a setup,property, or just a pointer to where I can find more info on the 'why'-part of the lock, I would be very grateful
database is SQL Server 2005 SP1 on win2k3
I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?
SQL 2005 Enterprise Edition SP 2
Thanks in advance.
Kay
Hi ,
I have code in SQL Server 2000 (DTS). what is same code to inactive execution status in SSIS
If returnValue <> "" Then
DTSPackage.Steps("DTSStep_DTSDataPumpTask_2").ExecutionStatus = DTSStepExecStat_Inactive
Else
DTSPackage.Steps("DTSStep_DTSDataPumpTask_3").ExecutionStatus = DTSStepExecStat_Inactive
End If
Thanks,
Raja
hi all,
i have a snapshot replication running on multiple publisher singel subscriber running on sql2k. i'm frequently encountering these error.
the subscription has been marked inactive and must be reinitialized.
no synch
i've set the subcription to never expire in the publications property tab.
thanks
Does anyone know if there is a 3rd party tool available to disconnect user logins after being inactive for a period of time? Is this something that can be custom written?
Thanks for any assistance, Jim
hi,i've just restored my db and when i try to login with a user (even ifthe user is "sa"), an error occours.The error is "User sa is currently inactive"How can i solve this problem?Thanks,Massimo
View 1 Replies View RelatedI' d like to list all inactive clients.Inactive client is a client who hasn't had invoice for 2 months.I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clientsaddresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !=''). I select only invoices with date after the interesting date.So now I can list all active clients but i can't negate this resultset to get all inactive clients:SELECT dok_PlatnikId, adr_Nazwa,adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWystFROM vwDok4FSFZGridINNER JOIN adr__EwidON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiek tuWHEREadr__Ewid.adr_Nazwa !=''AND(dok_DataWyst >= convert(datetime,'10/03/2007'))GROUP BY dok_PlatnikId, adr_Nazwa,adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWystIs there any way to negate this set?1. Sorry for my English2. I would appreciate any help:)
View 1 Replies View RelatedIs anyone else having an issue with SSRS menus not being able to be clicked on?
When I roll the mouse over the menu item, it highlights, but when I click, nothing happens.
It affects the top menu items, and any right click menu.
This is an intermittent problem, but it does happen daily. I have to shut down VS to fix.
BobP
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
View 4 Replies View RelatedA Contractor has a list of Contracting Companies that he has worked for in his history.
For each row in the "Contracting Company" table, there is an Active Date and an Inactive Date. Each Contracting Company cannot have overlapping dates.
The last row in the table for that Contractor may have a null Inactive Date. That just means that he doesn't know when his contract will end.
In Report Builder, I can access the "Last Active Date", because there is a pre-built formula for "Last Active Date". However, whenever I access the prebuilt formula "Last Inactive Date" - I get the inactive date for the second-last row, because the last row has a null value for the Inactive Date! I want to get the Inactive Date for the last row - not the second last row!
How to do this easily? Thanks!
Hi AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG
View 1 Replies View RelatedI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance
the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG
How to query in SQL to retrieve all the disabled Windows Users which are available as records(only) in the SQL Server ?
View 9 Replies View RelatedHi,
I€™m hoping someone might be able to help me.
I have a Development Machine running Windows XP Professional SP2, with SQL Server 2005 including Reporting Services. I am developing an ASP.NET application using VS 2005, and am including Server Side Reports from SQL Server. Everything is working well, except when viewing the Reports.
The Reports when viewed from Reporting Services€™ Report Manager or from within the ASP.NET application only display the first page of the report, displaying the total number of pages as 0. As well, most of the functions on the report toolbar appear to be disabled. This occurs for any report viewed, even sample reports such as the Adventure Works Samples.
The problem only occurs when viewed from within Internet Explorer on the Development Machine. To overcome the problem, I have upgraded SQLServer to SP2, and removed and reinstalled Internet Explorer, but it made no difference. It appears to be related to the rsClientPrint ActiveX addon which should have been installed when the print icon was first selected. It appears not to have installed properly, but up to that point everything worked OK. Selecting the Print icon (or any other icon) since nothing happens. I€™m guessing here, but I suspect that the rsClientPrint addon needs to be reinstalled, but I don€™t know how to go about it. It is not listed within IE addon (where it is on other machines connecting to the Development Machine), nor is it listed within the €œC:WindowsDownloaded Program Files€?. But it is not being downloaded when the Print Icon is selected. I have checked all the Security settings for the environment to ensure that ActiveX programs are downloaded, and everything appears to be in order.
Would anyone be familiar with this problem or have any suggestions on how to go about resolving it? Any help would be appreciated as it is causing significant problems. Strange though, the reports appear fine when viewed from within the Server Manager.
I need to create a trigger to meet following conditions.
When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.
When parent record is deleted child records needs to be inactivated in table.
I am getting this error when I run the case statement below. I think I have to convert at some point, but not sure where or how.Â
Case when m.dispo = 2 then 'Inactive' else m.value end 'Score'
Hi !
How do I check when my database was last used ? I have two old databases that I think is not in use, but I want to check to make sure before I delete them or take them "offline".
TIA MAttias
Hello,
I am performing a number of queries on a database and inputting the results of those queries into a target database.
These queries are performed inside a number of data flows.
I would like to do two things:
1) Check that the target database can be seen before entering the dataflows. If not, wait X minutes and then retry.
2) If, for some reason, SSIS loses connectivity to the target database during the process, I would like to, is possible, roll back any current transactions and start over.
Unfortunately my knowledge of SSIS is very limited, so if anyone could lend a hand I would appreciate it.
Best regards,
Simon
Hello, Everyone:
There are some databases in a SQL Server. How to check,
1. User database numbers
2. Backup plan
3. Configuration
4. User database size
5. Nightly process
Thanks a lot
ZYT
I was wondering if it was possible to check what server database you are currently connected to, using T-SQL, when executing commands in Query Analyzer. Even though you choose the server and database when connection, sometimes by habit you may connect to the wrong server & DB, and execute an .SQL file. Is it possible to put a line of T-SQL at the beginning that performs this pseudo-code, as a safety feature:-- While in Query Analyzer with a certain .SQL file open:If current server <> 'TheCorrectServer' and current DB <> 'TheCorrectDB' then cancel this .SQL file execution.
View 3 Replies View RelatedHi everyone,
I am looking for a way to check to see if a database is a snapshot or not. You can for example check different properties of a database by running the following:
SELECT DATABASEPROPERTYEX('databasename','Recovery')
SELECT DATABASEPROPERTYEX('databasename','IsInStandBy')
SELECT DATABASEPROPERTYEX('databasename','Status')
So I am trying to see if there is a way to check for a snapshot
Thanks,
Reghardt
Hi,
I'm wondering is there a command that I can use to check if a certain value exists in the database? Something that returns true or false perhaps?
SELECT * FROM Customers WHERE ID ='1'
Ok so that's simple, but how can I return a true or false if '1' is a value that exists in the db?
thanks.
I want to query my databases to find out if Auto Shrink is set. I know the status column in sysdatabases holds this value as a bit setting but I don't know how can I check this value using a query.
Canada DBA
im trying to set up maintenance plan for the check database integrity...
In sql 2000 you get a nice little log in SQL Logs
DBCC CHECKDB (WSS_Search_db3) executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
But in SQL 2005
Im not getting a nice log of this but getting it against some system database and not the database i selected
Date1/22/2008 5:19:43 PM
LogSQL Server (Current - 1/22/2008 5:19:00 PM)
Sourcespid84
Message
DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by XXX found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
Anyone got this to work?
Trying to get the same message i got for SQL 2000 or at least so i can confirm it ran.
Hi everybody,Is there way to check what were the last modifys to specified database??I mean the last insertion, deletion ........... .. and in which tables.Thanks for advance--"Imagination is more important than knolwege" (Albert Einshtein)Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200507/1
View 6 Replies View RelatedhelloWhat query shoul I send to SQL serwer ( in transact SQL language ) to checkdoes some database exist on serwer ? It similar to problem "does some tableexist in database" - resolve to it is query:use db_silnikIF EXISTS (SELECT * FROM prad)PRINT 'table exist'but what is the query to check does some database exist on serwer ?best regardsAdam
View 2 Replies View RelatedHello,I am trying to check the integrity of SQL database. The 'problem' withSQL server is that it's fixing the DB automatically after restore so Ineed a way to do it offline, much like using eseutil on Exchangedatabases.Thanks in advance.
View 1 Replies View Related
I have SQL Server 2005 Standard Edition.
How to calculate database transactions per second?
Thank you,
Gish